I’ve done a SQL test for a job I’ve applied for. There is no actual data or actual SQL being run, it’s simply providing the code. This is the test:
Context
Customers receive a mortgage quote as part of their journey. At which point it’s “open” and then later it hopefully gets “completed” but could also be “closed lost”.
Question
For each day of 2024 we want to know how many Mortgages quotes are open (i.e. have gotten
to the quote stage but have not yet gotten “completed” or been “closed lost)”?
Table Schema
Table Name mortgage_journey
Table Description
We have a mortgage journey table where each row represents a mortgage and fields with the
prefix step__
represent steps in our mortgage funnel.
Field | Description |
---|---|
mortgage_journey_id | Unique identifier for a mortgage |
advisor_name | Which Mortgage Advisor was assigned to the lead |
mortgage_type | Was it for a new purchase or a remortgage |
step__quote_ts | When was the mortgage quote given? |
step__completed_ts | Did the Mortgage get to what we can consider the final step in our funnel? Null if not completed |
step__closed_lost_ts | Have we lost the mortgage and at what date/time did that |
happen? Null if still open or completed |
My initial thought was this seems too easy. An open mortgage is where step__completed_ts and step__closed_lost_ts is null and we want the date to be 2024. Then it’s simply having a count for each date.
Then it occurred to me that if there were no quotes on a given date it won’t be in the table, so I decided to generate a date array for 2024 and then left join the results onto it so each day is visible.
I’ve created this sort of date table many times and know it works.
This is my solution:
with date_array_table as(
select
generate_date_array('2024-01-01','2024-12-31',interval 1 day) as date
),
date_table as(
select
days
from date_array_table
cross join unnest(date) as date
),
quote_table as(
select
cast(step__quote_ts as date) as quote_date,
count(*) as quotes
from `mortgage_journey`
where year(step__quote_ts) = 2024
step__completed_ts is null
and step__closed_lost_ts is null
group by 1
)
select
t1.date
t2.quotes
from date_table t1
left join quote_table t2
on t1.date = t2.quote_date
order by 1
They have now got back to me and told me my solution is wrong but they are unable to provide any feedback.
What is wrong with this solution?