AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 2
SQL ยท Lesson 100 of 126

Retention

Do new customers come back?.

โฑ 2 min readโ–ถ Video at 2:21:04๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Here's lesson one hundred, and a question every online business asks. Of the customers who bought for the first time in a year, how many came back within ninety days? Customers grouped by when they started are called a cohort.

Retention
From the video: Retention
  • Lesson 100!
  • Did new customers come back?
  • Grouped into cohorts

A cohort report

Step one finds each customer's first order. Then we join back to all their orders. For each cohort year, we count customers, and count those with another order between one and ninety days after their first. Count distinct, with case inside, counts each returning customer only once. Run it. In twenty twenty four, one hundred and thirteen of two hundred and seventy one came back within ninety days. That's about forty two percent.

query.sqlsql
WITH firsts AS (
    SELECT customer_id, MIN(order_date) AS first_order
    FROM orders
    GROUP BY customer_id
)
SELECT strftime('%Y', f.first_order) AS cohort,
       COUNT(DISTINCT f.customer_id)  AS customers,
       COUNT(DISTINCT CASE
               WHEN julianday(o.order_date) - julianday(f.first_order)
                    BETWEEN 1 AND 90
               THEN f.customer_id END) AS back_in_90_days
FROM firsts AS f
JOIN orders AS o ON o.customer_id = f.customer_id
GROUP BY cohort;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ cohort โ”‚ customers โ”‚ back_in_90_days โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2024   โ”‚ 271       โ”‚ 113             โ”‚
โ”‚ 2025   โ”‚ 192       โ”‚ 68              โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

What interviewers look for

Retention questions test everything at once. CTEs, joins, date maths, case, and count distinct. If you can explain this query step by step, you're ready for most analyst interviews.

CTEsfirst order
JOINback to all orders
Date mathswithin 90 days
COUNT(DISTINCT CASE)count each once

Summary

A cohort report groups customers by when they started, and tracks whether they come back. Next, interview questions about ideas, not code.

Key takeaways
  • First order = cohort
  • Back within 90 days
  • COUNT(DISTINCT CASE ...)
Next up: Interview Questions, Part 3 โ€” the theory questions