← Intuit Interview Insights

Intuit·Data Scientist·Technical Phone Screen·Senior

SeniorPrefer not to say
May 2026Remote

Summary

Interviewed for a Data Scientist role at Intuit and got hit with a pretty involved SQL question that combined window functions, date spine generation, and year-over-year math all in one query. The problem looked manageable at first glance but the edge cases piled up fast.

Questions Asked (1)

Q1

Write a single PostgreSQL query that returns monthly counts of new paid subscribers from June 2019 through the latest month in the data, along with the same-month prior-year count, absolute year-over-year change, and year-over-year percent change. A new paid subscriber is a company with a non-null subscriptiondate in that month, excluding any company that ever had a free subscription. Include months with zero subscribers and handle the case where no prior-year data exists.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs
Author's notes

This one took me longer to mentally parse than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions, then build the query in logical steps: identify new paid subscribers, aggregate monthly counts, generate a complete month series, and finally join to the prior year to compute YoY metrics. Use CTEs for readability and ensure edge cases like zero months and missing prior-year data are handled with LEFT JOINs and COALESCE.

Pro tip: Mention that you would validate the exclusion logic by checking for companies that ever had a free subscription, and consider using a NOT EXISTS clause for performance. Also, explicitly state how you handle the latest month and ensure the date range is dynamic.

1. Clarify requirements and schema

Confirm the table structure, definitions of 'new paid subscriber', 'free subscription', and how to identify the latest month. Ask about timezone and date truncation.

2. Identify new paid subscribers

Write a subquery to select companies with non-null subscriptiondate in the target months, excluding those that ever had a free subscription (e.g., using NOT EXISTS or LEFT JOIN with NULL check).

3. Aggregate monthly counts

Group the filtered companies by month (date_trunc('month', subscriptiondate)) and count distinct companies to get monthly new paid subscriber counts.

4. Generate complete month series

Use generate_series to create a continuous sequence of months from June 2019 to the latest month in the data, then LEFT JOIN the aggregated counts to include zero months.

5. Compute year-over-year metrics

Self-join the monthly counts to the same month in the prior year (using date subtraction or interval), then calculate absolute change (current - prior) and percent change ((current - prior)/prior * 100), handling NULLs with COALESCE.

Key Points to Mention

  • Use of CTEs for modular and readable query structure
  • Exclusion of companies with any free subscription using NOT EXISTS or anti-join
  • Handling missing months with generate_series and LEFT JOIN
  • Correctly computing YoY metrics with NULL handling for missing prior-year data
  • Performance considerations: indexing on subscriptiondate and company ID, avoiding unnecessary subqueries
  • Dynamic determination of the latest month from the data rather than hardcoding

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