Advanced SQL — Case Studies¶
These are end-to-end scenarios that mirror real analyst work. Each one starts with a business question, walks through the analytical approach, and shows the SQL solution. Study the approach, not just the query.
Case Study 1 — Cohort Retention Analysis¶
Business context: An e-commerce company wants to know how well it retains customers. Specifically: of customers who first ordered in each month, what percentage came back and ordered again in the following months?
Analytical approach: 1. For each customer, find their first order month (acquisition cohort) 2. For each subsequent order, calculate how many months after acquisition it occurred 3. Count how many customers from each cohort ordered in each subsequent month 4. Express as a percentage of the cohort size
WITH
-- Step 1: first order month for each customer
first_orders AS (
SELECT
customer_id,
MIN(order_date) AS first_order_date,
DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort_month
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
),
-- Step 2: all orders with months since first purchase
all_orders AS (
SELECT
o.customer_id,
fo.cohort_month,
TIMESTAMPDIFF(
MONTH, fo.first_order_date, o.order_date
) AS months_since_first
FROM orders o
JOIN first_orders fo ON o.customer_id = fo.customer_id
WHERE o.status = 'completed'
),
-- Step 3: cohort size
cohort_sizes AS (
SELECT cohort_month, COUNT(DISTINCT customer_id) AS cohort_size
FROM first_orders
GROUP BY cohort_month
)
-- Step 4: retention rates
SELECT
ao.cohort_month,
cs.cohort_size,
ao.months_since_first,
COUNT(DISTINCT ao.customer_id) AS retained_customers,
ROUND(
COUNT(DISTINCT ao.customer_id) * 100.0 / cs.cohort_size, 1
) AS retention_pct
FROM all_orders ao
JOIN cohort_sizes cs ON ao.cohort_month = cs.cohort_month
GROUP BY ao.cohort_month, cs.cohort_size, ao.months_since_first
ORDER BY ao.cohort_month, ao.months_since_first;
Sample output:
| cohort_month | cohort_size | months_since_first | retained_customers | retention_pct |
|---|---|---|---|---|
| 2024-01 | 45 | 0 | 45 | 100.0 |
| 2024-01 | 45 | 1 | 28 | 62.2 |
| 2024-01 | 45 | 2 | 19 | 42.2 |
| 2024-01 | 45 | 3 | 14 | 31.1 |
Case Study 2 — Product Performance Dashboard¶
Business context: The merchandising team wants a single view of each product's performance — sales volume, revenue, margin, and inventory health.
WITH product_sales AS (
SELECT
o.product_id,
COUNT(*) AS times_ordered,
SUM(o.quantity) AS units_sold,
SUM(o.quantity * o.unit_price) AS gross_revenue,
SUM(o.quantity * o.unit_price * (1 - COALESCE(o.discount_pct, 0) / 100)) AS net_revenue,
SUM(o.quantity * p.cost_price) AS total_cost
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY o.product_id
)
SELECT
p.product_id,
p.product_name,
p.category,
p.unit_price,
p.stock_qty,
COALESCE(ps.times_ordered, 0) AS times_ordered,
COALESCE(ps.units_sold, 0) AS units_sold,
COALESCE(ps.net_revenue, 0) AS net_revenue,
COALESCE(ps.net_revenue - ps.total_cost, 0) AS gross_profit,
ROUND(
COALESCE((ps.net_revenue - ps.total_cost) / NULLIF(ps.net_revenue, 0) * 100, 0), 1
) AS margin_pct,
-- Inventory health
CASE
WHEN p.stock_qty = 0 THEN 'Out of Stock'
WHEN p.stock_qty < ps.units_sold * 0.5 THEN 'Low Stock'
WHEN p.stock_qty > ps.units_sold * 3 THEN 'Overstocked'
ELSE 'Healthy'
END AS inventory_status
FROM products p
LEFT JOIN product_sales ps ON p.product_id = ps.product_id
ORDER BY net_revenue DESC;
NULLIF prevents division by zero
NULLIF(ps.net_revenue, 0) returns NULL when revenue is 0, so the division produces NULL instead of a divide-by-zero error. Then COALESCE(..., 0) converts that NULL to 0.
Case Study 3 — Customer Segmentation (RFM)¶
Business context: Marketing wants to segment customers for targeted campaigns. RFM (Recency, Frequency, Monetary) is the industry-standard approach.
WITH
rfm_raw AS (
SELECT
customer_id,
MAX(order_date) AS last_order_date,
COUNT(*) AS frequency,
SUM(order_total) AS monetary
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
),
rfm_scores AS (
SELECT
customer_id,
DATEDIFF('2024-03-01', last_order_date) AS recency_days,
frequency,
monetary,
NTILE(5) OVER (ORDER BY DATEDIFF('2024-03-01', last_order_date) ASC) AS r_score, -- lower recency = better
NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
FROM rfm_raw
)
SELECT
customer_id,
recency_days,
frequency,
ROUND(monetary, 2) AS monetary,
r_score,
f_score,
m_score,
r_score + f_score + m_score AS rfm_total,
CASE
WHEN r_score >= 4 AND f_score >= 4 THEN 'Champions'
WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
WHEN r_score >= 4 AND f_score <= 2 THEN 'Recent Customers'
WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost Customers'
ELSE 'Potential Loyalists'
END AS segment
FROM rfm_scores
ORDER BY rfm_total DESC;
Sample output:
| customer_id | recency_days | frequency | monetary | r_score | f_score | m_score | segment |
|---|---|---|---|---|---|---|---|
| C003 | 8 | 12 | 2195.50 | 5 | 5 | 5 | Champions |
| C001 | 15 | 9 | 1456.25 | 5 | 4 | 4 | Champions |
| C012 | 45 | 8 | 2840.00 | 4 | 4 | 5 | Loyal Customers |
Case Study 4 — Month-over-Month Revenue with Running Totals¶
Business context: Finance wants a monthly P&L view with growth rates and year-to-date totals.
WITH monthly AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
YEAR(order_date) AS year,
SUM(order_total) AS revenue,
COUNT(DISTINCT customer_id) AS unique_customers,
COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m'), YEAR(order_date)
)
SELECT
month,
revenue,
unique_customers,
orders,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month) * 100, 1
) AS mom_growth_pct,
SUM(revenue) OVER (
PARTITION BY year
ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_revenue
FROM monthly
ORDER BY month;