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.

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?