← Intuit Interview Insights

Intuit·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jul 2026

Summary

SQL-heavy technical screen for a Data Scientist role at Intuit. The problem was a single multi-part question built around a subscription analytics table, escalating from basic cohort aggregation to YoY growth rates to a paid-only filter variant. Felt more like a take-home in spirit but was done live.

Questions Asked (3)

Q1

Given a user-level table with signup and subscription dates, write a query that returns, for each calendar month of signups, the total signups, the count who ever subscribed, and the conversion rate.

Product Analytics & MetricsData Modeling
Author's notes

The cohort grouping part was fine, DATE_TRUNC on signup_date and a conditional count on subscription_date not being null.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Clarify the table schema and definitions (e.g., what counts as 'ever subscribed', how to handle nulls, and whether to include months with zero signups). Then write a SQL query that groups by signup month, counts distinct users, counts distinct users with a non-null subscription date, and computes the ratio. Finally, discuss edge cases and potential improvements like using window functions or CTEs for readability.

Pro tip: Mention that conversion rate should be calculated as a percentage and consider using NULLIF to avoid division by zero. Also, proactively discuss how to handle time zones or date truncation to ensure accurate monthly grouping.

1. Clarify requirements and assumptions

Ask about the table structure, definitions of 'ever subscribed', and whether to include all calendar months or only those with signups. Confirm the desired output format for conversion rate.

2. Design the query logic

Plan to group by the signup month (using DATE_TRUNC or equivalent), count distinct user IDs for total signups, count distinct user IDs where subscription date is not null for ever subscribed, and compute the ratio.

3. Write the SQL query

Use a single SELECT with GROUP BY, employing COUNT(DISTINCT) and a CASE statement or filtered aggregate for subscribed users. Ensure proper handling of NULLs and division by zero.

4. Validate and discuss edge cases

Mention potential issues like users who subscribed before signing up (data quality), multiple subscriptions, or missing months. Suggest ways to handle them, such as using a calendar table or COALESCE.

5. Optimize and present

Consider performance implications (e.g., indexing on signup date) and readability (using CTEs). Present the final query clearly and explain each part.

Key Points to Mention

  • Use DATE_TRUNC('month', signup_date) to group by calendar month.
  • Count distinct users for total signups to avoid duplicates.
  • Define 'ever subscribed' as having a non-null subscription_date, and count distinct users accordingly.
  • Calculate conversion rate as (subscribed_count / total_signups) * 100, using NULLIF to prevent division by zero.
  • Consider whether to include months with zero signups; if so, use a calendar table or generate_series.
  • Discuss data quality checks, such as ensuring subscription_date >= signup_date.

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

Q2

Extend the query to compute year-over-year subscription rate growth for each month, defined as the percentage change in conversion rate versus the same calendar month in the prior year. Return NULL if the prior year's month isn't in the data.

Product Analytics & MetricsData Modeling
Author's notes

This is where it got genuinely tricky.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a window function like LAG to access the conversion rate from the same month in the prior year, then compute the percentage change. Ensure the calculation returns NULL when the prior year's data is missing.

Pro tip: Explicitly handle the case where the prior year's month is absent by using LAG with a date offset and checking for NULL, rather than relying on self-joins which can be error-prone.

1. Understand the metric

Clarify that conversion rate is typically conversions divided by sessions or users, and year-over-year growth is the percentage change from the same month last year.

2. Aggregate monthly conversion rates

Group data by year and month, and compute the conversion rate for each month.

3. Access prior year's value

Use the LAG window function with an offset of 12 months (or partition by month and order by year) to retrieve the conversion rate from the same month in the previous year.

4. Compute percentage change

Calculate (current_rate - prior_rate) / prior_rate * 100, ensuring that if prior_rate is NULL, the result is NULL.

5. Handle missing data

Verify that months without prior year data return NULL, and consider using COALESCE or CASE statements if needed.

Key Points to Mention

  • Use of window functions like LAG for time-series comparisons
  • Definition of conversion rate and its calculation
  • Handling of NULL values when prior year data is missing
  • Partitioning by month to ensure correct alignment
  • Potential need to filter out incomplete months
  • Consideration of seasonality in subscription metrics

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

Q3

Now assume the table includes both free and paid subscription types. Recompute the YoY growth metric but treat only paid subscribers as 'converted'. How does your query change?

Product Analytics & MetricsPricing & Monetization
Author's notes

Honestly just adding a filter on subscription_type in the conditional count.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the definition of 'converted' as paid subscribers only, then modify the query to filter for paid subscription type in the conversion metric while keeping the overall subscriber base for the denominator. Recompute YoY growth by comparing the paid conversion rate (paid subscribers / total subscribers) year-over-year.

Pro tip: Mention that you would validate the change by checking if the paid conversion rate aligns with business expectations and consider segmenting by acquisition channel to uncover hidden trends.

1. Clarify the metric definition

Confirm that 'converted' now refers exclusively to paid subscribers, and that the denominator remains all subscribers (free + paid) or total users, depending on the original metric.

2. Adjust the query logic

Modify the conversion calculation to count only rows where subscription_type = 'paid' as conversions, while still counting all subscribers for the base.

3. Recompute YoY growth

Calculate the paid conversion rate for each year and compute the year-over-year growth as (current_year_rate - prior_year_rate) / prior_year_rate.

4. Validate and interpret

Sanity-check results against business context, and consider if the change in definition affects trend interpretation or requires further segmentation.

Key Points to Mention

  • Definition of 'converted' as paid subscribers only
  • Filtering condition: subscription_type = 'paid' in the conversion count
  • Denominator remains total subscribers (free + paid) unless specified otherwise
  • YoY growth formula: (current_year_paid_rate - prior_year_paid_rate) / prior_year_paid_rate
  • Potential need to handle NULL or missing subscription types
  • Impact on business insights: paid conversion rate may show different trends than overall conversion

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