SQL Analytics Patterns
Recurring analyst SQL shapes — sessionization, gaps-and-islands, retention, cohorts, and funnels.
11 questions
JuniorCodeVery commonThree-step funnel — users at each step and step-to-step conversion
Three-step funnel — users at each step and step-to-step conversion
Count distinct users per step with conditional aggregation — COUNT(DISTINCT user_id) FILTER (WHERE step = 'view') and the same for cart and purchase. Each conversion divides the next step's users by the previous step's — cart over view, purchase over cart.
Common mistakes
- ✗Dividing each step by the total base instead of the previous step
- ✗Using COUNT(*) rows instead of COUNT(DISTINCT user_id)
- ✗Confusing step-to-step conversion with end-to-end conversion
Follow-up questions
- →How would you also report the end-to-end view→purchase conversion?
- →Why prefer FILTER over three separate grouped subqueries?
MiddleCodeVery commonCohort retention triangle — signup-month cohorts by months since signup
Cohort retention triangle — signup-month cohorts by months since signup
Bucket each signup into its cohort month and size the cohort with COUNT(*). Compute months_since from the year-and-month gap between activity month and cohort month, then divide distinct actives per (cohort, months_since) by that fixed cohort size — not by a moving base.
Common mistakes
- ✗Using the previous month's actives as a moving denominator
- ✗Approximating months elapsed as day-difference over 30
- ✗Sizing the cohort from month-0 actives instead of all signups
Follow-up questions
- →Why is a fixed cohort-size denominator better than a moving one?
- →How would you left-fill months where a cohort had zero actives?
JuniorCodeCommonDay-1 retention in SQL — the share of signups active the next day
Day-1 retention in SQL — the share of signups active the next day
LEFT JOIN activity on activity_date = signup_date + 1, then divide the distinct matched users by all signups. The LEFT JOIN keeps non-returners in the denominator, so the ratio is retained users over the whole signup cohort, not over returners only.
Common mistakes
- ✗Using INNER JOIN, which drops non-returners from the denominator
- ✗Counting any later activity instead of the exact next day
- ✗Dividing activity rows instead of distinct users
Follow-up questions
- →How would you extend this to day-7 or day-30 retention?
- →Why does an INNER JOIN silently change the denominator here?
JuniorCodeCommonClassify each user active this month as new or returning in one query
Classify each user active this month as new or returning in one query
GROUP BY user_id over all activity. A user is new when MIN(activity_date) lands in the current month, else returning. Restrict output to users active this month with HAVING MAX(activity_date) >= date_trunc('month', CURRENT_DATE). Their first-ever activity decides the label.
Common mistakes
- ✗Deciding new vs returning by visit count this month
- ✗Using MAX(activity_date) instead of MIN for the first-seen check
- ✗Filtering to this month before computing the all-time first activity
Follow-up questions
- →Why must the first-seen check run over all history, not just this month?
- →How would you add a third
resurrectedlabel for lapsed users?
MiddleDebuggingCommonAt-least-once events inflate the daily active count — deduplicate idempotently
At-least-once events inflate the daily active count — deduplicate idempotently
COUNT(user_id) tallies every delivered row, so duplicate deliveries inflate it. Collapse to one row per event_id first — DISTINCT ON (event_id) or ROW_NUMBER() = 1 — then COUNT(DISTINCT user_id). Keying dedup on the delivery-unique id is idempotent, so re-runs are stable.
Common mistakes
- ✗Deduplicating the aggregated output instead of the raw event rows
- ✗Believing COUNT(*) or COUNT(DISTINCT user_id) alone removes redelivered rows
- ✗Blaming null keys rather than legitimate at-least-once redelivery
Follow-up questions
- →Why is deduplicating on event_id idempotent while COUNT(DISTINCT user_id) is not enough?
- →How would you pick which duplicate row to keep when their payloads differ?
MiddleCodeCommonFunnel with a 24-hour window — a purchase must follow a view within 24h
Funnel with a 24-hour window — a purchase must follow a view within 24h
Join each user's purchases to their views on purchase_time > view_time AND purchase_time <= view_time + interval '24 hours'. A user converts when one such pair exists, so count DISTINCT converters over distinct viewers — attributing each user once.
Common mistakes
- ✗Ignoring the window and counting any view-plus-purchase user
- ✗Bracketing first view to last purchase instead of per-pair gaps
- ✗Using an absolute gap that lets a purchase precede its view
Follow-up questions
- →How would you make sure each converter is counted only once?
- →How would you attribute the purchase to its most recent qualifying view?
MiddleCodeOccasionalLongest streak of consecutive active days per user
Longest streak of consecutive active days per user
Gaps-and-islands: per user, subtract a date-ordered ROW_NUMBER() from the date — consecutive days share one offset, a constant island key. Group by user and that key, COUNT(*) the days per run, then take the MAX run length per user.
Common mistakes
- ✗Counting all day-after-day rows instead of the longest single run
- ✗Using the first-to-last span as if activity were continuous
- ✗Treating total distinct active days as the longest streak
Follow-up questions
- →Why does
activity_date - ROW_NUMBER()stay constant within a run? - →How would you also return the start and end date of that streak?
MiddleCodeOccasionalSplit an event stream into sessions with a 30-minute inactivity gap
Split an event stream into sessions with a 30-minute inactivity gap
Per user, take LAG(event_time) ordered by time. Flag a new session when the gap to the previous event exceeds 30 minutes, or when there is no previous event (LAG is NULL). Summing those start flags per user counts the sessions — each flag marks exactly one session boundary.
Common mistakes
- ✗Dividing the total active span by 30 minutes instead of inspecting gaps
- ✗Bucketing by a clock-aligned 30-minute window rather than inter-event gap
- ✗Counting close events as sessions instead of counting session starts
Follow-up questions
- →How would you assign a session id to each individual event?
- →How does the first event per user avoid being missed as a session start?
SeniorCodeOccasionalLabel every user every month as new, retained, churned, or resurrected
Label every user every month as new, retained, churned, or resurrected
Cross-join every user with a month spine, left-join activity for an active flag per user-month. LAG(active) gives last month; a running SUM(active) counts prior actives. The four lifecycle labels follow from comparing this month's flag to the previous month's.
Common mistakes
- ✗Labeling from the current month's visit count without prior-month context
- ✗Believing churn needs a separate anti-join because it has no row
- ✗Using a day-level event gap instead of month-over-month presence
Follow-up questions
- →How does the month spine let a churned (row-less) month be classified?
- →How would you produce a monthly growth-accounting summary from these labels?
SeniorCodeRareMulti-entry funnel — attribute each user once to their furthest step in the window
Multi-entry funnel — attribute each user once to their furthest step in the window
Rank the steps (view=1, cart=2, purchase=3) and take MAX(step_rank) per user in the window — collapsing every re-entry into one furthest step. Then count users with FILTER (WHERE max_step >= k) per k — a cumulative funnel counting each user once, at their deepest step.
Common mistakes
- ✗Assuming distinct-per-step counts already handle re-entry correctly
- ✗Taking the chronologically last event as the furthest step
- ✗Summing step ranks so repeat visits outrank a single deep pass
Follow-up questions
- →Why can a plain distinct-per-step funnel double-count a re-entering user?
- →How would you restrict the furthest step to a single session, not the whole window?
SeniorCodeRareN-day vs rolling retention — write both and show where the numbers diverge
N-day vs rolling retention — write both and show where the numbers diverge
Bracket retention matches activity_date = signup_date + 7; rolling matches activity_date >= signup_date + 7. Both divide distinct matched users by the cohort. Rolling is always at least bracket — a user active on day 10 but not day 7 passes rolling yet fails the exact-day test.
Common mistakes
- ✗Swapping which definition uses the exact-day vs the on-or-after predicate
- ✗Assuming both definitions yield the same retention number
- ✗Reading rolling as activity up to day 7 rather than day 7 onward
Follow-up questions
- →Why is rolling retention always greater than or equal to bracket retention?
- →Which definition better reflects a product used only a few times a month?
