DashCoreHub
Mission M26 · SQL · 35 min
Customer cohort retention
Request from your manager
The loyalty program manager asks: "Of the customers who placed their first order in January 2024, what percentage came back and ordered again in February 2024?"
Data
Download the practice file(s):
Steps
- Create a CTE first_orders with each customer's MIN(order_date).
- Keep only the January 2024 cohort: substr(first_date, 1, 7) = '2024-01'.
- Count the cohort size.
- Join the cohort back to orders and count DISTINCT customers with an order in '2024-02'.
- Divide returning customers by cohort size and multiply by 100.0 (use 100.0, not 100, to avoid integer division).
- Extra challenge: repeat for '2024-03' to see month-2 retention.
Check your answer
What % of the January 2024 cohort ordered again in February 2024?
Show solution
283 customers made their first order in January 2024; 59 of them ordered in February 2024, so month-1 retention = 59 / 283 = 20.85%.
WITH first_orders AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
GROUP BY customer_id
),
cohort AS (
SELECT customer_id
FROM first_orders
WHERE substr(first_date, 1, 7) = '2024-01'
),
returned AS (
SELECT DISTINCT o.customer_id
FROM orders o
JOIN cohort c ON c.customer_id = o.customer_id
WHERE substr(o.order_date, 1, 7) = '2024-02'
)
SELECT (SELECT COUNT(*) FROM cohort) AS cohort_size,
(SELECT COUNT(*) FROM returned) AS returned,
ROUND(100.0 * (SELECT COUNT(*) FROM returned)
/ (SELECT COUNT(*) FROM cohort), 2) AS retention_pct;Answer: 20.85 %