Articles
Advanced SQL for Data Scientist Interviews: Six Auditable Patterns
Practice advanced PostgreSQL with verified rolling distinct, Top-N, islands, pivots, D1 retention, and recursive identity examples.
Advanced SQL interview problems become difficult when a query must preserve the right population and grain while rows are ranked, deduplicated, pivoted, or assigned to a cohort.
This guide is for Data Scientist interviews and uses PostgreSQL throughout. Every pattern follows the same review path: inspect the input rows, run the complete query, trace how the rows change, and compare the exact output. The examples are deliberately small enough to audit by hand.
What advanced SQL means in a Data Scientist screen
Six patterns cover the work in this guide. Each has a different correctness question.
| Pattern | Prompt signal | Correctness question |
|---|---|---|
| Rolling distinct users | “active in the last N days” | Are users deduplicated across days, and are missing dates present? |
| Top-N per group | “top two in each category” | Do ties count, or must exactly N rows return? |
| Gaps and islands | “consecutive months” or “streak” | Is the input already one row per entity and period? |
| Conditional pivot | “one column per status” | Do the output columns reconcile to the total? |
| D1 retention | “returned the next day” | Is the cohort fully observed and activity deduplicated? |
| Monthly retention after merges | “canonical user” or “merged account” | Are identities, flags, signup times, and the observation horizon consistent? |
Window functions are useful in several of these patterns, but the window is not the goal. It is one step in a query whose population and grain must remain explicit. The deeper mechanics are covered in SQL window functions.
Rolling distinct users and Top-N with ties
Pattern 1: rolling distinct users need a calendar spine
The input has a duplicate event for user 1 on August 1 and no activity at all on August 3.
| user_id | activity_date |
|---|---|
| 1 | 2025-08-01 |
| 2 | 2025-08-01 |
| 1 | 2025-08-01 |
| 1 | 2025-08-02 |
| 3 | 2025-08-02 |
| 2 | 2025-08-04 |
| 3 | 2025-08-04 |
| 4 | 2025-08-04 |
The query first creates one row for every calendar date. It then compares the sum of daily active users with a distinct count over the underlying three-day range.
WITH calendar AS (
SELECT generate_series(
DATE '2025-08-01',
DATE '2025-08-04',
INTERVAL '1 day'
)::date AS dt
),
daily AS (
SELECT activity_date AS dt,
COUNT(DISTINCT user_id) AS dau
FROM activity
GROUP BY activity_date
)
SELECT c.dt,
COALESCE(d.dau, 0) AS dau,
SUM(COALESCE(d.dau, 0)) OVER (
ORDER BY c.dt
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS sum_of_daily_dau_3d,
(
SELECT COUNT(DISTINCT a.user_id)
FROM activity a
WHERE a.activity_date BETWEEN c.dt - 2 AND c.dt
) AS distinct_active_3d
FROM calendar c
LEFT JOIN daily d ON d.dt = c.dt
ORDER BY c.dt;
| dt | dau | sum_of_daily_dau_3d | distinct_active_3d |
|---|---|---|---|
| 2025-08-01 | 2 | 2 | 2 |
| 2025-08-02 | 2 | 4 | 3 |
| 2025-08-03 | 0 | 4 | 3 |
| 2025-08-04 | 3 | 5 | 4 |
The two rightmost columns answer different questions. Daily counts add to 5 on August 4 because users 2 and 3 appear on more than one date in the range. The distinct range count returns 4 people.
Pattern 2: Top-N must define tie behavior
Audio products A and B have the same profit. That tie determines whether “top two” means two rows or two distinct profit values.
| product | category | profit_usd |
|---|---|---|
| A | audio | 900.00 |
| B | audio | 900.00 |
| C | audio | 400.00 |
| D | audio | 100.00 |
| E | video | 700.00 |
| F | video | 250.00 |
This version returns the top two distinct profit values per category and keeps all ties.
WITH ranked AS (
SELECT product,
category,
profit_usd,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY profit_usd DESC
) AS profit_rank
FROM product_profit
)
SELECT category, product, profit_usd, profit_rank
FROM ranked
WHERE profit_rank <= 2
ORDER BY category, profit_rank, product;
| category | product | profit_usd | profit_rank |
|---|---|---|---|
| audio | A | 900.00 | 1 |
| audio | B | 900.00 | 1 |
| audio | C | 400.00 | 2 |
| video | E | 700.00 | 1 |
| video | F | 250.00 | 2 |
Before writing the ranking function, ask what should happen on a tie. That answer chooses between row number, rank, and dense rank.
Deduplicated islands and a pivot that reconciles
Pattern 3: repair the grain before finding islands
The subscription snapshot contains the same u2 month twice. A row-number island query run directly on this input would treat that duplicate as another month position.
| user_id | month_start | is_active |
|---|---|---|
| u1 | 2025-05-01 | 1 |
| u1 | 2025-06-01 | 1 |
| u1 | 2025-07-01 | 0 |
| u1 | 2025-08-01 | 1 |
| u2 | 2025-06-01 | 1 |
| u2 | 2025-06-01 | 1 |
| u2 | 2025-07-01 | 1 |
| u2 | 2025-08-01 | 1 |
The first CTE restores one row per user-month. Only then does the query assign an island key.
WITH deduped AS (
SELECT user_id,
month_start,
MAX(is_active) AS is_active
FROM sub_month
GROUP BY user_id, month_start
),
active_months AS (
SELECT user_id,
month_start,
12 * EXTRACT(YEAR FROM month_start)
+ EXTRACT(MONTH FROM month_start)
- ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY month_start
) AS island_key
FROM deduped
WHERE is_active = 1
)
SELECT user_id,
MIN(month_start) AS run_start,
MAX(month_start) AS run_end,
COUNT(*) AS months_in_run
FROM active_months
GROUP BY user_id, island_key
ORDER BY user_id, run_start;
| user_id | run_start | run_end | months_in_run |
|---|---|---|---|
| u1 | 2025-05-01 | 2025-06-01 | 2 |
| u1 | 2025-08-01 | 2025-08-01 | 1 |
| u2 | 2025-06-01 | 2025-08-01 | 3 |
The same family also covers event sessions and repeated states, but those versions use a boundary flag instead of month arithmetic. See gaps and islands for those variants.
The duplicate rows in this fixture agree, so MAX(is_active) restores their shared value. If duplicate snapshots conflict, replace that step with an explicit source-precedence or latest-record rule before building islands.
Pattern 4: a fixed pivot must reconcile to its total
The trip status is a closed four-value set in this fixture. Every status needs a column, and the four status counts must add up to total_trips.
| trip_id | city | status |
|---|---|---|
| 1 | Austin | completed |
| 2 | Austin | completed |
| 3 | Austin | cancelled_by_rider |
| 4 | Austin | driver_no_show |
| 5 | Denver | completed |
| 6 | Denver | cancelled_by_driver |
SELECT city,
COUNT(*) AS total_trips,
COUNT(*) FILTER (WHERE status = 'completed') AS completed,
COUNT(*) FILTER (WHERE status = 'cancelled_by_rider') AS cancel_rider,
COUNT(*) FILTER (WHERE status = 'cancelled_by_driver') AS cancel_driver,
COUNT(*) FILTER (WHERE status = 'driver_no_show') AS driver_no_show,
ROUND(
100.0 * COUNT(*) FILTER (WHERE status = 'completed')
/ NULLIF(COUNT(*), 0),
1
) AS completion_pct
FROM trips
GROUP BY city
ORDER BY city;
| city | total_trips | completed | cancel_rider | cancel_driver | driver_no_show | completion_pct |
|---|---|---|---|---|---|---|
| Austin | 4 | 2 | 1 | 0 | 1 | 50.0 |
| Denver | 2 | 1 | 0 | 1 | 0 | 50.0 |
If production can receive new status values, add an other_status count or validate the status dimension before publishing the report.
Bounded D1 retention with a literal underscore
Pattern 5: define the last fully observed cohort day
data_through is the last complete event date. A D1 cohort is mature only when the following day is also complete, so the latest eligible signup date is data_through - 1.
| data_through |
|---|
| 2025-09-01 |
| user_id | signup_date |
|---|---|
| a1 | 2025-08-26 |
| a2 | 2025-08-26 |
| a3 | 2025-08-27 |
| bot_1 | 2025-08-26 |
| botX1 | 2025-08-26 |
| user_id | event_time |
|---|---|
| a1 | 2025-08-27 08:00:00 |
| a1 | 2025-08-27 21:30:00 |
| a2 | 2025-08-29 10:00:00 |
| a3 | 2025-08-28 07:15:00 |
| bot_1 | 2025-08-27 00:01:00 |
| botX1 | 2025-08-27 09:00:00 |
The product rule excludes IDs beginning with the literal prefix bot_. The underscore is escaped so botX1 remains eligible. Null IDs are treated as invalid and excluded explicitly.
WITH params AS (
SELECT DATE '2025-09-01' AS data_through
),
eligible_users AS (
SELECT u.user_id, u.signup_date
FROM app_users u
CROSS JOIN params p
WHERE u.signup_date BETWEEN p.data_through - 7 AND p.data_through - 1
AND u.user_id IS NOT NULL
AND u.user_id NOT LIKE 'bot\_%' ESCAPE '\'
),
daily_activity AS (
SELECT DISTINCT user_id, event_time::date AS event_date
FROM app_events
)
SELECT u.signup_date AS cohort_day,
COUNT(*) AS cohort_size,
COUNT(a.user_id) AS retained_d1,
ROUND(100.0 * COUNT(a.user_id) / NULLIF(COUNT(*), 0), 1) AS d1_pct
FROM eligible_users u
LEFT JOIN daily_activity a
ON a.user_id = u.user_id
AND a.event_date = u.signup_date + 1
GROUP BY u.signup_date
ORDER BY u.signup_date;
| cohort_day | cohort_size | retained_d1 | d1_pct |
|---|---|---|---|
| 2025-08-26 | 3 | 2 | 66.7 |
| 2025-08-27 | 1 | 1 | 100.0 |
Without DISTINCT, a1 would count twice. Without the explicit escape, an underscore acts as a one-character wildcard and botX1 would be removed even though it does not start with bot_.
Recursive identity merges and mature monthly cohorts
Pattern 6: resolve identity and eligibility before retention
This example uses a retroactive merge policy: every historical row from a child account maps to its terminal parent. merged_ts remains in the input for auditability but does not change that mapping. The merge graph must have at most one parent per child. A chain must end at a terminal identity; cyclic chains fail to produce a terminal mapping and should be rejected upstream.
Flag policy is also explicit. Employee and test raw accounts contribute neither signup membership nor activity, even if they merge into an eligible parent. Events must occur on or after that raw account's signup and before the observation horizon.
| user_id | signup_ts | is_employee | is_test |
|---|---|---|---|
| u1 | 2025-01-05 00:00:00 | false | false |
| u2 | 2025-01-20 00:00:00 | false | false |
| u3 | 2025-02-10 00:00:00 | false | false |
| u4 | 2025-02-14 00:00:00 | true | false |
| u5 | 2025-01-08 00:00:00 | false | false |
| u6 | 2025-02-20 00:00:00 | false | false |
| u7 | 2025-01-07 00:00:00 | false | false |
| child_user_id | parent_user_id | merged_ts |
|---|---|---|
| u7 | u5 | 2025-01-25 00:00:00 |
| u5 | u1 | 2025-02-01 00:00:00 |
| u4 | u3 | 2025-03-01 00:00:00 |
| user_id | event_ts |
|---|---|
| u1 | 2025-01-06 00:00:00 |
| u1 | 2025-02-03 00:00:00 |
| u2 | 2025-01-25 00:00:00 |
| u5 | 2025-03-04 00:00:00 |
| u7 | 2025-03-07 00:00:00 |
| u3 | 2025-02-11 00:00:00 |
| u3 | 2025-04-02 00:00:00 |
| u4 | 2025-02-15 00:00:00 |
| u6 | 2025-02-01 00:00:00 |
| u6 | 2025-02-21 00:00:00 |
| u6 | 2025-04-05 00:00:00 |
| data_through_exclusive | requested_max_offset |
|---|---|
| 2025-05-01 | 2 |
The recursive walk resolves u7 through u5 to u1. The grid emits only cells whose target month begins before May 1, so every displayed cell is mature.
WITH RECURSIVE
params AS (
SELECT DATE '2025-05-01' AS data_through_exclusive,
2 AS requested_max_offset
),
identity_walk(raw_id, current_id, path) AS (
SELECT p.user_id,
p.user_id,
ARRAY[p.user_id]::text[]
FROM user_profile p
UNION ALL
SELECT w.raw_id,
m.parent_user_id,
w.path || m.parent_user_id
FROM identity_walk w
JOIN user_merges m ON m.child_user_id = w.current_id
WHERE NOT (m.parent_user_id = ANY(w.path))
),
canonical AS (
SELECT w.raw_id,
w.current_id AS canonical_id
FROM identity_walk w
LEFT JOIN user_merges m ON m.child_user_id = w.current_id
WHERE m.child_user_id IS NULL
),
eligible_raw AS (
SELECT p.user_id,
p.signup_ts,
c.canonical_id
FROM user_profile p
JOIN canonical c ON c.raw_id = p.user_id
WHERE NOT p.is_employee
AND NOT p.is_test
),
identities AS (
SELECT canonical_id,
MIN(signup_ts) AS first_signup_ts
FROM eligible_raw
GROUP BY canonical_id
),
cohorts AS (
SELECT canonical_id,
DATE_TRUNC('month', first_signup_ts)::date AS cohort_month
FROM identities
WHERE first_signup_ts >= TIMESTAMP '2025-01-01'
AND first_signup_ts < TIMESTAMP '2025-03-01'
),
activity AS (
SELECT er.canonical_id,
DATE_TRUNC('month', e.event_ts)::date AS active_month
FROM eligible_raw er
JOIN identities i ON i.canonical_id = er.canonical_id
JOIN user_events e ON e.user_id = er.user_id
CROSS JOIN params p
WHERE e.event_ts >= er.signup_ts
AND e.event_ts >= i.first_signup_ts
AND e.event_ts < p.data_through_exclusive
GROUP BY er.canonical_id, DATE_TRUNC('month', e.event_ts)::date
),
offsets AS (
SELECT generate_series(0, p.requested_max_offset) AS month_number
FROM params p
),
grid AS (
SELECT cm.cohort_month,
o.month_number
FROM (SELECT DISTINCT cohort_month FROM cohorts) cm
CROSS JOIN offsets o
CROSS JOIN params p
WHERE (
cm.cohort_month
+ make_interval(months => o.month_number)
)::date < p.data_through_exclusive
)
SELECT g.cohort_month,
g.month_number,
COUNT(DISTINCT c.canonical_id) AS users_in_cohort,
COUNT(DISTINCT a.canonical_id) AS retained_users,
ROUND(
COUNT(DISTINCT a.canonical_id)::numeric
/ NULLIF(COUNT(DISTINCT c.canonical_id), 0),
4
) AS retention_rate
FROM grid g
JOIN cohorts c ON c.cohort_month = g.cohort_month
LEFT JOIN activity a
ON a.canonical_id = c.canonical_id
AND a.active_month = (
g.cohort_month
+ make_interval(months => g.month_number)
)::date
GROUP BY g.cohort_month, g.month_number
ORDER BY g.cohort_month, g.month_number;
| cohort_month | month_number | users_in_cohort | retained_users | retention_rate |
|---|---|---|---|---|
| 2025-01-01 | 0 | 2 | 2 | 1.0000 |
| 2025-01-01 | 1 | 2 | 1 | 0.5000 |
| 2025-01-01 | 2 | 2 | 1 | 0.5000 |
| 2025-02-01 | 0 | 2 | 2 | 1.0000 |
| 2025-02-01 | 1 | 2 | 0 | 0.0000 |
| 2025-02-01 | 2 | 2 | 2 | 1.0000 |
The February zero is present because the grid exists before activity is joined. The April cell is present because April is complete under the May 1 exclusive horizon. The recursive CTE is doing identity work, not merely organizing the query. See the CTE guide for the mechanics of recursive evaluation.
FAQ
Should I use a CTE or a subquery in an interview?
Use the form that makes the grain of each step easy to review. A CTE is useful when the solution has named stages such as deduplication, ranking, and final filtering. In PostgreSQL, planner behavior depends on version, references, and materialization options, so a CTE is not a performance promise.
Why can’t I sum daily active users for a rolling active-user count?
One person can be active on several days. Summing daily distinct counts counts that person once per active day. A rolling distinct metric must deduplicate identities across the whole rolling range.
Which ranking function should I choose for Top-N?
Use row number for exactly N rows and add a deterministic tiebreaker. Use dense rank for the top N distinct values with all ties. Use rank when ties should consume positions.
What should I clarify before writing retention SQL?
Clarify the cohort population, activity event, timezone, identity policy, pre-signup rule, observation horizon, and whether a missing period should be zero or omitted. Those choices determine the query before syntax does.
How should I practice these patterns?
Rebuild each query from the displayed input tables, predict the output before running it, then change one edge row. The SQL practice ladder provides progressively harder prompts once these six shapes are comfortable.