Interview › Cohort retention

SQL interview: signup cohort retention

A cohort is “who first appeared in the same bucket.” Retention is whether they came back.

Prompt. For each signup month, how many customers ordered again in later months?

Hint: MIN(order_date) per customer is the cohort

Your T-SQL

WITH first_seen AS (
  SELECT customer_id, CONVERT(char(7), MIN(order_date), 120) AS cohort
  FROM Orders
  GROUP BY customer_id
),
activity AS (
  SELECT DISTINCT customer_id, CONVERT(char(7), order_date, 120) AS ym
  FROM Orders
)
SELECT f.cohort, a.ym, COUNT(*) AS customers
FROM first_seen f
JOIN activity a ON a.customer_id = f.customer_id
GROUP BY f.cohort, a.ym
ORDER BY f.cohort, a.ym;

COUNT(*) here is customers, because activity is already DISTINCT. Do not double-count orders.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.

Clock in free