Search⌘ K
AI Features
Home
Courses
Grokking the Data Science Interview

Subqueries and CTEs

Explore how to compute precise retention and revenue metrics for monthly signup cohorts by structuring SQL queries with subqueries and CTEs. Understand staging intermediate results, validating data grains, and using SQLite date functions to ensure accuracy and readability in cohort analysis.

We'll cover the following...

The interviewer asks, “For each monthly signup cohort, calculate day-7 retention and average revenue per user (ARPU) during the first 30 days after signup.” Use the users(user_id, signup_date) and events(user_id, event_time, event_type, revenue) tables. The query assigns users to monthly signup cohorts, calculates a day-7 retention flag and 30-day revenue total for each user, and aggregates the results by cohort month. Because this dataset stores dates as text in SQLite, use SQLite date functions to parse and compare them. An ineffective approach combines these calculations into nested scalar subqueries without clearly separating the intermediate results:

SELECT
  strftime('%Y-%m-01', u.signup_date) AS cohort_month,
  COUNT(DISTINCT u.user_id) AS cohort_size,
  COUNT(DISTINCT CASE
    WHEN EXISTS (
      SELECT 1
      FROM events e
      WHERE e.user_id = u.user_id
        AND e.event_type = 'active'
        AND date(e.event_time) = date(u.signup_date, '+7 days')
    )
    THEN u.user_id
  END) * 1.0 / COUNT(DISTINCT u.user_id) AS day7_retention,
  SUM(
    COALESCE((
      SELECT SUM(e2.revenue)
      FROM events e2
      WHERE e2.user_id = u.user_id
        AND date(e2.event_time) >= date(u.signup_date)
        AND date(e2.event_time) <= date(u.signup_date, '+30 days')
    ), 0)
  ) * 1.0 / COUNT(DISTINCT u.user_id) AS arpu_30d
FROM users u
GROUP BY strftime('%Y-%m-01', u.signup_date);
Weak answer: Nested scalar subqueries hide the grain

The query can return an answer, but it hides the analysis units inside expressions. We cannot inspect whether a user has one retention record, whether revenue was summed once per user, or whether the denominator changed before the final GROUP BY.

The stronger answer states the contracts before writing the final aggregation. cohort_users has one row per user_id. user_day7_active has one row per user_id and a 0 or 1 flag. user_revenue_30d has one row per user_id and a revenue total. cohort_rollup has one row per cohort_month.

Explicit grains turn events into cohort metrics
Explicit grains turn events into cohort metrics

This diagram illustrates the transition from event-level data to user-level metrics. Calculate retention flags and revenue totals for each user before aggregating the results by cohort. A candidate could explain: “I'll create one record per user containing their signup cohort, day-7 retention flag, and 30-day revenue total. Then I'll aggregate these records by cohort month.” To validate the results, check row counts and distinct keys at each stage to detect unexpected duplication or record loss.

Build each grain deliberately

The staged query uses common table expressions (CTEs), named query results that exist for the duration of one statement. Chaining CTEs does not make the business logic different. It makes each intermediate result observable.

cohort_users derives a stable cohort month with strftime('%Y-%m-01', signup_date). user_day7_active uses EXISTS so repeated activity events on day 7 cannot create duplicate user rows. user_revenue_30d groups events by user_id before any join back to cohort membership. The final CTE joins two user-grain results to cohort_users on user_id, then computes the cohort metrics.

The retention denominator is every row in cohort_users. The retention numerator is SUM(day7_active). The ARPU denominator remains that same cohort size, including users with no revenue events. ... ...