Loading…
Loading…
SQL tutorial · Expert
⏱️ 30 mins read
Expert analytics SQL involves cohort analysis (grouping users by when they joined), retention analysis (% of users who return), and funnel analysis (conversion rates through a sequence of steps). These patterns appear in product analytics, growth engineering, and data science roles.
-- Cohort: group users by first event date
-- Retention: join to later activity
-- Funnel: SUM(CASE WHEN step THEN 1 END)-- Cohort Analysis: group customers by their first-order month
WITH first_order AS (
SELECT customer_id,
MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id
)
SELECT
DATE_FORMAT(f.first_order_date, '%Y-%m') AS cohort_month,
DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
COUNT(DISTINCT o.customer_id) AS active_customers
FROM orders o
JOIN first_order f ON o.customer_id = f.customer_id
GROUP BY DATE_FORMAT(f.first_order_date, '%Y-%m'),
DATE_FORMAT(o.order_date, '%Y-%m');
-- Day-1 Retention from the logins table (introduced below)
SELECT
a.login_date AS cohort_day,
COUNT(DISTINCT a.customer_id) AS day0,
COUNT(DISTINCT b.customer_id) AS day1
FROM logins a
LEFT JOIN logins b
ON a.customer_id = b.customer_id
AND b.login_date = a.login_date + INTERVAL 1 DAY
GROUP BY a.login_date;
-- Funnel Analysis: the order pipeline in one pass
SELECT
COUNT(*) AS orders_placed,
COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
COUNT(DISTINCT CASE WHEN status = 'shipped'
AND customer_id IN (SELECT customer_id FROM orders
GROUP BY customer_id HAVING COUNT(*) > 1)
THEN customer_id END) AS repeat_shipped_customers
FROM orders;| customer_id | login_date |
|---|---|
| 1 (Karl) | 2024-01-01, 01-02, 01-03, 03-01 |
| 2 (Ines) | 2024-02-01, 02-02 |
| 3 (Omar) | 2024-04-01 |
| 4 (Sofia) | 2024-05-01 |
| 5 (Maike) | 2024-03-01, 03-02, 03-03, 03-04 |
| id | customer_id | status | total | order_date |
|---|---|---|---|---|
| 1 | 1 | shipped | 240.00 | 2024-01-15 |
| 2 | 2 | shipped | 89.90 | 2024-02-03 |
| 3 | 1 | pending | 430.00 | 2024-03-11 |
| 4 | 3 | shipped | 59.50 | 2024-04-02 |
| 5 | 4 | cancelled | 120.00 | 2024-05-20 |
A cohort is just a GROUP BY (Topic 9) whose output becomes another query's dimension. The CTE (Topic 17) computes each customer's first-order month; joining orders back onto it tags every order with its customer's cohort. Two aggregations, one picture of retention.
-- Output of the example's cohort query:
-- cohort_month | order_month | active_customers
-- 2024-01 | 2024-01 | 1 (Karl's first order)
-- 2024-01 | 2024-03 | 1 (Karl came BACK)
-- 2024-02 | 2024-02 | 1 (Ines)
-- 2024-04 | 2024-04 | 1 (Omar)
-- 2024-05 | 2024-05 | 1 (Sofia)
-- Karl is the only returning customer — five orders, one repeatDay-N retention asks: who logged in on day 0 AND day N? That is Topic 15's self-join with a twist — the join condition is on dates, not IDs alone. LEFT JOIN keeps day-0 rows whose day-1 never arrives, and counting non-NULL right rows gives the retention numerator.
-- Day-1 retention from the example, traced:
-- Karl: Jan 1 → Jan 2 exists ✓ (and Jan 2 → Jan 3 ✓)
-- Ines: Feb 1 → Feb 2 ✓
-- Maike: Mar 1 → Mar 2 ✓ (and 2→3, 3→4 ✓)
-- Omar: Apr 1 → nothing ✗ Sofia: May 1 → nothing ✗
-- Day-0 rows: 9 · Day-1 matches: 7 → 7/9 ≈ 78%A funnel counts how many entities survive each stage. No analytics table required: Topic 11's conditional aggregation computes all stages in one pass. Our order funnel: 5 placed → 3 shipped → 1 shipped customer who has ordered more than once (Karl — his repeat order is still pending, which the funnel honestly reports).
-- From the example, traced on ShopCo:
-- orders_placed: 5 (all orders)
-- shipped: 3 (1, 2, 4)
-- repeat_shipped_customers: 1 (Karl — order 1 shipped, 2 orders total)
-- Note what the funnel DOESN'T say: Karl's repeat order is pending.Not accounting for timezone differences in date grouping — all dates should be converted to the same timezone before analysis. Also: cohort sizes vary widely by month, so always show % retention alongside absolute numbers.
Product analytics companies (Airbnb, Uber, Meta) ask cohort and retention questions constantly. Know how to build a retention matrix (day 0 through day 30 for each cohort). The date-minus-row-number trick for consecutive days is a must-know.
1. What defines a customer's cohort in the ShopCo example?
2. Tracing the cohort query on ShopCo — how many customers ever come BACK after their first order?
3. Day-1 retention is computed with…?
4. The order funnel (placed → shipped → repeat shipped customer) returns…?
5. Why did this topic introduce the logins table?
6. How should timezones be handled in cohort/retention queries?
Return the holder name and net flow (sum of amounts) of the account with the highest net flow.
⚡ Solve it in the SQL playground →Store timestamps in UTC, convert to the business timezone at the query boundary — and do it ONCE, before any DATE() truncation. A session at 23:30 local is a different day in UTC, and a timezone mix will silently scatter users across the wrong cohorts.
The numbers are, the mechanics aren't. Small data is the best time to learn the pattern — you can verify every row by hand. On ShopCo, you can SEE that only Karl repeated; production cohort queries compute exactly the same thing over 10M rows.