Intuit·Data Scientist·Technical Phone Screen·Senior

Senior

May 2026

Summary

SQL-heavy technical screen for a Data Scientist role at Intuit. One big question, lots of moving parts, and I spent way too long second-guessing the spine generation before I even touched the lag join.

Questions Asked (1)

Write a single SQL query that returns monthly new subscriber counts starting from June 2019, including year-over-year comparisons, a generated month spine (no hardcoded end dates), zero-filled months, a 12-month lag join, safe division for percentage deltas, and a flag indicating whether each month is a partial month based on the data watermark. A new subscriber is defined as a company whose earliest non-null subscription date falls in that month.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs

Author's notes

This was a lot to hold in your head at once.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining the base subscriber cohort using a subquery that finds each company's earliest non-null subscription date, then aggregate to monthly counts. Build a dynamic month spine from the minimum date to the data watermark using a recursive CTE or date functions, left join the counts, and use a self-join with a 12-month lag to compute YoY deltas with safe division. Finally, flag partial months by comparing each month to the watermark.

Pro tip: Always anchor the month spine to the data watermark (e.g., MAX(subscription_date)) rather than CURRENT_DATE to avoid misaligned partial months in historical snapshots, and explicitly state that the partial month flag should be used to filter out incomplete months in downstream reporting.

1. Identify new subscribers

Use a subquery to find each company's earliest non-null subscription date, then filter to those with a date on or after June 2019. This defines the cohort of new subscribers.

2. Aggregate monthly counts

Group the cohort by the month of their earliest subscription date and count distinct companies to get the number of new subscribers per month.

3. Generate a dynamic month spine

Create a series of months from June 2019 up to the data watermark (e.g., using a recursive CTE or GENERATE_DATE_ARRAY) without hardcoding the end date, then left join the monthly counts and zero-fill missing months.

4. Compute year-over-year comparison

Self-join the monthly counts to the same table shifted by 12 months (using a lag window function or a date offset) to get the prior-year count for each month.

5. Calculate percentage delta and partial month flag

Use safe division (e.g., NULLIF or CASE) to compute the YoY percentage change, and flag months as partial if the month equals the month of the data watermark (or if the watermark is not the last day of the month).

Key Points to Mention

Use of a recursive CTE or date generation function to create a dynamic month spine without hardcoded end dates.
Definition of new subscriber as the earliest non-null subscription date per company, requiring a subquery with MIN and filtering out NULLs.
Zero-filling missing months by left joining the spine to aggregated counts and using COALESCE or IFNULL.
12-month lag join using a window function (LAG) or self-join on date offset to enable year-over-year comparison.
Safe division using NULLIF or CASE to avoid division by zero when computing percentage deltas.
Partial month flag based on the data watermark (e.g., MAX(subscription_date)) to indicate incomplete data for the current month.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.