Level 6 · Boss: cohort retention

Cohort analysis is the classic analyst take-home: it needs CTEs, window functions, date logic and a clear definition, all at once. This boss builds up to it.

Step 1: share of total

Aggregate per category, then divide each row by the grand total:

ROUND(100.0 * net_revenue / SUM(net_revenue) OVER (), 1) AS pct_of_total

An empty OVER () makes the window the whole result, so every row sees the same total. No subquery, no join. The shares should add up to about 100; if they add to far more, a join fanned out.

Step 2: time to second order

ROW_NUMBER numbers each customer’s orders in time order. LAG then gives each order’s gap from the previous one, and keeping order_number = 2 leaves exactly one row per returning customer: the gap between their first and second orders. The order_id tie-breaker guards against two orders with the same timestamp.

The starter shows one customer’s numbered orders. Inspecting a single entity like this is how you sanity check a window before aggregating it.

Customers who never placed a second order are not in the average. That is correct for “how long until they come back”, but present it next to how many never came back at all, or the number looks better than reality.

Step 3: cohort retention

Three definitions make a cohort table:

• Cohort: the month of a customer’s first completed order. MIN(order_month) OVER (PARTITION BY customer_id) attaches it to every one of their rows without collapsing them.
• Activity: a customer is active in a month if they placed at least one completed order. The customer_months CTE uses SELECT DISTINCT on purpose, to get one row per customer per month, the correct grain for “active or not”.
• Retained in month 1: active in the calendar month right after the cohort month.

COUNT(DISTINCT CASE WHEN order_month = CAST(cohort_month + INTERVAL '1 month' AS DATE)
                    THEN customer_id END) AS retained_m1

COUNT(DISTINCT CASE ...) counts customers who meet the condition. The CASE returns NULL otherwise, and COUNT ignores NULLs.

The cohort filter is applied after the window, in the outer query. Filtering orders to 2025 before computing MIN would assign customers who first bought in 2024 to a 2025 cohort, the same trap as with LAG and moving averages.

Dialect notes

Adding a month: DuckDB and Postgres accept date + INTERVAL '1 month' (the result is a timestamp, hence the cast). BigQuery uses DATE_ADD(cohort_month, INTERVAL 1 MONTH), MySQL DATE_ADD(cohort_month, INTERVAL 1 MONTH), Snowflake DATEADD(month, 1, cohort_month).

Taking it further

The full cohort table has a column for every month offset (m1, m2, m3, …), usually computed with DATEDIFF in months between order_month and cohort_month, grouped by cohort and offset, then pivoted. In an interview, getting month 1 right, with clear definitions stated out loud, is worth more than a rushed full grid.