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

Practice Set: Metrics and Cohorts

Explore six common SQL problems related to user metrics and cohort analysis, including weekly active users, day-7 retention, churn rate, revenue per active user, funnel drop-off, and cohort retention curves. Learn to identify subtle query errors, predict outputs, and correct bugs while understanding the importance of defining metric assumptions and output granularity. This practice set equips you to validate SQL queries and communicate your reasoning clearly in data science interviews.

We'll cover the following...

This practice set covers six common data science SQL interview problems: weekly active users, day-7 retention, churn, revenue per active user, funnel drop-off, and cohort retention curves. Most exercises provide queries with subtle errors and ask you to predict their outputs, identify bugs, or correct the SQL rather than write a query from scratch. For each problem, review the requirements, define the expected output granularity, and attempt a solution before checking the hints. Each exercise provides a complete solution and an explanation of the reasoning you should communicate during an interview. The final problem is the most challenging and includes follow-up questions commonly asked during interviews.

Each problem requires defining the metric and its assumptions before writing SQL. For example, when calculating day-7 retention, determine whether the denominator includes all users who signed up during the cohort period or only those active on their signup day.

The schema contains users(user_id, signup_date), sessions(session_id, user_id, started_at), events(event_id, user_id, event_time, event_type), and purchases(purchase_id, user_id, purchased_at, amount). sessions and events each have many rows per user. purchases has many rows per buyer. A metric query must therefore state its final grain before joining raw tables.

Practice-set events schema and one-to-many relationships
Practice-set events schema and one-to-many relationships

Problem 1: Weekly active users

Scenario: Predict the output of a query intended to compute weekly active users for the last four weeks. Then identify the week-boundary bug.

Assume one output row per calendar week. A user counts as active when events.event_type is session_start; multiple qualifying events from the same user in one week still count once. The supplied query groups event_time by a text-derived week label, so inspect whether events on the boundary land in the intended week.

Supplied query:

SELECT
  date(event_time, 'weekday 1') AS week_start,
  COUNT(DISTINCT user_id) AS active_users
FROM events
WHERE event_type = 'session_start'
  AND date(event_time, 'weekday 1') BETWEEN '2026-09-07' AND '2026-09-28'
GROUP BY week_start
ORDER BY week_start;
Supplied query: Weekday 1 shifts Sunday events forward

The sample events table has 11 rows. Ten are session_start events and one is a page_view:

  • Event 1001: user 1, 2026-09-06 23:30 (Sunday), session_start

  • Event 1002: user 1, 2026-09-07 00:10 (Monday), session_start

  • Event 1003: user 2, 2026-09-13 22:00 (Sunday), session_start

  • Event 1004: user 2, 2026-09-14 00:05 (Monday), session_start

  • Event 1005: user 2, 2026-09-14 12:00 (Monday), page_view

  • Event 1006: user 3, 2026-09-20 09:00 (Sunday), session_start

  • Event 1007: user 3, 2026-09-20 10:00 (Sunday), session_start

  • Event 1008: user 3, 2026-09-21 00:01 (Monday), session_start

  • Event 1009: user 4, 2026-09-27 23:59 (Sunday), session_start

  • Event 1010: user 4, 2026-09-28 00:00 (Monday), session_start

  • Event 1011: user 5, 2026-09-29 15:00 (Tuesday), session_start

Predict the output before reading on.

Question: Given the 11-row sample events table, what does the supplied query return, and which expression causes the week-boundary bug?

The numerator is COUNT(DISTINCT user_id), not the number of event rows.

Solution

The supplied query returns 1 active user in each of the four weeks. date(x, 'weekday 1') moves a date forward to the next Monday, so every Sunday event is labeled with the following week: user 1’s Sunday 2026-09-06 event lands in the week of 2026-09-07, and user 2’s, user 3’s and user 4’s Sunday events shift the same way.

A date expression must encode the stated week policy. A label like date(event_time, 'weekday 1') can look plausible while shifting Sunday events into the following week, so one reporting week’s activity is split across two labels.

A defensible repair derives week_start explicitly and filters the four-week window against that same derived value. Here, :as_of_date is the reporting date; the query includes its ...