SQL for Data Science: Cohorts, Funnels, A/B Tests & Growth
Cohort retention, funnel conversion, A/B testing, MoM growth — every metric that drives a product or board meeting starts as a SQL query. The data scientists at Stripe, Airbnb, Notion, and Linear write these patterns every week. This lesson teaches the exact templates.
Learning Objectives
After this lesson, you will be able to:
Build cohort analysis queries that track user retention over time
Calculate retention rates and identify when users churn
Construct funnel conversion queries that measure drop-off at each step
Analyze A/B test results with statistical rigor using pure SQL
Compute month-over-month and week-over-week growth rates
Segment users by behavior using SQL-driven RFM analysis
Write time-series queries with rolling averages and cumulative sums
Detect common analytical pitfalls: peeking, Simpson's paradox, and survivorship bias
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
Cohort analysis groups users by when they first appeared (their "cohort") and tracks their behavior over time. It answers: "Do users who signed up this month behave differently from users who signed up last month?"
-- Find each user's cohort (month they first appeared)
SELECT
user_id,
STRFTIME('%Y-%m', MIN(event_date)) AS cohort_month
FROM user_events
GROUP BY user_id;
-- Full cohort retention analysis
WITH user_cohorts AS (
SELECT
user_id,
STRFTIME('%Y-%m', MIN(event_date)) AS cohort_month,
MIN(event_date) AS first_event
FROM user_events
GROUP BY user_id
),
user_activity AS (
SELECT
uc.user_id,
uc.cohort_month,
-- How many months after signup did this activity occur?
CAST(
(JULIANDAY(STRFTIME('%Y-%m-01', e.event_date)) -
JULIANDAY(STRFTIME('%Y-%m-01', uc.first_event))) / 30
AS INTEGER) AS period_number
FROM user_events e
JOIN user_cohorts uc ON e.user_id = uc.user_id
)
SELECT
cohort_month,
period_number,
COUNT(DISTINCT user_id) AS active_users
FROM user_activity
WHERE period_number >= 0 AND period_number <= 6
GROUP BY cohort_month, period_number
ORDER BY cohort_month, period_number;
-- Cohort retention as percentages
WITH user_cohorts AS (
SELECT
user_id,
STRFTIME('%Y-%m', MIN(event_date)) AS cohort_month,
MIN(event_date) AS first_event
FROM user_events
GROUP BY user_id
),
cohort_sizes AS (
SELECT cohort_month, COUNT(*) AS cohort_size
FROM user_cohorts
GROUP BY cohort_month
),
user_activity AS (
SELECT
uc.cohort_month,
CAST(
(JULIANDAY(STRFTIME('%Y-%m-01', e.event_date)) -
JULIANDAY(STRFTIME('%Y-%m-01', uc.first_event))) / 30
AS INTEGER) AS period_number,
COUNT(DISTINCT uc.user_id) AS active_users
FROM user_events e
JOIN user_cohorts uc ON e.user_id = uc.user_id
GROUP BY uc.cohort_month, period_number
)
SELECT
ua.cohort_month,
cs.cohort_size,
ua.period_number,
ua.active_users,
ROUND(100.0 * ua.active_users / cs.cohort_size, 1) AS retention_pct
FROM user_activity ua
JOIN cohort_sizes cs ON ua.cohort_month = cs.cohort_month
WHERE ua.period_number BETWEEN 0 AND 6
ORDER BY ua.cohort_month, ua.period_number;
The output looks like a retention table:
cohort_month
cohort_size
period_0
period_1
period_2
period_3
2024-01
1,200
100%
45%
32%
28%
2024-02
1,500
100%
48%
35%
31%
2024-03
1,800
100%
52%
38%
--
If newer cohorts retain better, your product is improving. If they retain worse, you have a problem.
Loading visualization...
Try it! Modify the cohort query to use weeks instead of months. Change STRFTIME('%Y-%m', ...) to STRFTIME('%Y-%W', ...) and divide the JULIANDAY difference by 7 instead of 30. Weekly cohorts give you faster signal on product changes.
Funnel analysis tracks how many users complete each step in a multi-step process. Every SaaS company tracks funnels like: Visit -> Signup -> Onboarding -> First Action -> Subscription.
sql
-- E-commerce checkout funnel
WITH funnel AS (
SELECT
session_id,
MAX(CASE WHEN event = 'view_product' THEN 1 ELSE 0 END) AS step_1_view,
MAX(CASE WHEN event = 'add_to_cart' THEN 1 ELSE 0 END) AS step_2_cart,
MAX(CASE WHEN event = 'begin_checkout' THEN 1 ELSE 0 END) AS step_3_checkout,
MAX(CASE WHEN event = 'enter_payment' THEN 1 ELSE 0 END) AS step_4_payment,
MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS step_5_purchase
FROM events
WHERE event_date >= DATE('now', '-30 days')
GROUP BY session_id
)
SELECT
'View Product' AS step, SUM(step_1_view) AS users,
ROUND(100.0 * SUM(step_1_view) / SUM(step_1_view), 1) AS pct
FROM funnel
UNION ALL
SELECT
'Add to Cart', SUM(step_2_cart),
ROUND(100.0 * SUM(step_2_cart) / SUM(step_1_view), 1)
FROM funnel
UNION ALL
SELECT
'Begin Checkout', SUM(step_3_checkout),
ROUND(100.0 * SUM(step_3_checkout) / SUM(step_1_view), 1)
FROM funnel
UNION ALL
SELECT
'Enter Payment', SUM(step_4_payment),
ROUND(100.0 * SUM(step_4_payment) / SUM(step_1_view), 1)
FROM funnel
UNION ALL
SELECT
'Purchase', SUM(step_5_purchase),
ROUND(100.0 * SUM(step_5_purchase) / SUM(step_1_view), 1)
FROM funnel;
This produces a table showing exactly where users drop off:
step
users
pct
View Product
10,000
100.0%
Add to Cart
3,200
32.0%
Begin Checkout
1,800
18.0%
Enter Payment
1,200
12.0%
Purchase
950
9.5%
The biggest drop-off is View -> Cart (68% drop). That is where to focus optimization.
What Do You Think?
In the funnel above, 3,200 users added to cart but only 1,800 began checkout. The step-over-step conversion is 56%. If you improve this one step to 70% conversion, how many more purchases would you expect (assuming downstream rates stay the same)?
Improving cart-to-checkout from 56% to 70% means 3,200 * 0.70 = 2,240 checkout users (up from 1,800). That is 440 more users entering checkout. The downstream rate (checkout to purchase) is 950/1,800 = 52.8%. So 440 * 0.528 = ~232 more purchases. This is how product teams prioritize: calculate the revenue impact of fixing each funnel step.
A/B tests compare two variants to see which performs better. SQL can compute the key metrics:
sql
-- A/B test results: does the new checkout page increase conversion?
SELECT
experiment_variant,
COUNT(*) AS total_users,
SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS conversions,
ROUND(100.0 * SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS conversion_rate,
ROUND(AVG(revenue), 2) AS avg_revenue_per_user,
ROUND(SUM(revenue), 2) AS total_revenue
FROM ab_test_results
WHERE experiment_name = 'checkout_redesign_v2'
AND experiment_start >= '2024-03-01'
GROUP BY experiment_variant;
-- Quick significance check using conversion rates and sample sizes
-- For a proper z-test, you need: conversion rates, sample sizes, and pooled proportion
WITH metrics AS (
SELECT
experiment_variant,
COUNT(*) AS n,
SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS conversions,
CAST(SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS REAL) / COUNT(*) AS rate
FROM ab_test_results
WHERE experiment_name = 'checkout_redesign_v2'
GROUP BY experiment_variant
)
SELECT
a.rate AS control_rate,
b.rate AS variant_rate,
ROUND((b.rate - a.rate) / a.rate * 100, 2) AS lift_pct,
a.n AS control_n,
b.n AS variant_n,
-- Rule of thumb: need at least 100 conversions per variant
CASE
WHEN a.conversions < 100 OR b.conversions < 100
THEN 'Insufficient data'
WHEN ABS(b.rate - a.rate) / SQRT(a.rate * (1 - a.rate) / a.n + b.rate * (1 - b.rate) / b.n) > 1.96
THEN 'Statistically significant (p < 0.05)'
ELSE 'Not significant -- need more data'
END AS significance
FROM metrics a, metrics b
WHERE a.experiment_variant = 'control'
AND b.experiment_variant = 'variant_b';
Run the same analysis live. The playground below ships with a small ab_test table — try changing the conversion rates and re-running to see when the test crosses into "significant."
-- MoM revenue growth
WITH monthly_revenue AS (
SELECT
STRFTIME('%Y-%m', order_date) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY STRFTIME('%Y-%m', order_date)
)
SELECT
curr.month,
curr.revenue AS current_revenue,
prev.revenue AS previous_revenue,
ROUND(curr.revenue - COALESCE(prev.revenue, 0), 2) AS absolute_change,
CASE
WHEN prev.revenue IS NULL THEN NULL
WHEN prev.revenue = 0 THEN NULL
ELSE ROUND(100.0 * (curr.revenue - prev.revenue) / prev.revenue, 1)
END AS mom_growth_pct
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
ON curr.month = STRFTIME('%Y-%m', DATE(prev.month || '-01', '+1 month'))
ORDER BY curr.month;
-- 7-day rolling average of daily signups
WITH daily_signups AS (
SELECT
DATE(created_at) AS signup_date,
COUNT(*) AS signups
FROM users
GROUP BY DATE(created_at)
)
SELECT
d1.signup_date,
d1.signups AS daily_signups,
ROUND(AVG(d2.signups), 1) AS rolling_7day_avg
FROM daily_signups d1
JOIN daily_signups d2
ON d2.signup_date BETWEEN DATE(d1.signup_date, '-6 days') AND d1.signup_date
GROUP BY d1.signup_date, d1.signups
ORDER BY d1.signup_date;
-- Cumulative revenue (running total) over time
SELECT
DATE(order_date) AS order_day,
SUM(total) AS daily_revenue,
SUM(SUM(total)) OVER (ORDER BY DATE(order_date)) AS cumulative_revenue,
COUNT(*) AS daily_orders,
SUM(COUNT(*)) OVER (ORDER BY DATE(order_date)) AS cumulative_orders
FROM orders
GROUP BY DATE(order_date)
ORDER BY order_day;
What Do You Think?
A company's monthly revenue: Jan=$100K, Feb=$110K, Mar=$99K. What is the MoM growth for March?
March MoM growth is -10%, calculated against February (the immediately preceding month): ($99K - $110K) / $110K = -10%. MoM always compares to the previous month, not to the first month or any other baseline. This is an important distinction -- the company grew from Jan to Feb (+10%) but shrank from Feb to Mar (-10%).
RFM (Recency, Frequency, Monetary) is a classic segmentation framework that classifies customers using three dimensions:
Recency: How recently did they purchase? (lower = better)
Frequency: How often do they purchase? (higher = better)
Monetary: How much do they spend? (higher = better)
sql
-- RFM segmentation
WITH rfm_base AS (
SELECT
customer_id,
CAST(JULIANDAY('now') - JULIANDAY(MAX(order_date)) AS INTEGER) AS recency_days,
COUNT(DISTINCT order_id) AS frequency,
SUM(total) AS monetary
FROM orders
WHERE order_date >= DATE('now', '-365 days')
GROUP BY customer_id
),
rfm_scores AS (
SELECT
customer_id,
recency_days,
frequency,
monetary,
-- Score 1-5 using quintiles (simplified with CASE)
CASE
WHEN recency_days <= 30 THEN 5
WHEN recency_days <= 60 THEN 4
WHEN recency_days <= 120 THEN 3
WHEN recency_days <= 240 THEN 2
ELSE 1
END AS r_score,
CASE
WHEN frequency >= 20 THEN 5
WHEN frequency >= 10 THEN 4
WHEN frequency >= 5 THEN 3
WHEN frequency >= 2 THEN 2
ELSE 1
END AS f_score,
CASE
WHEN monetary >= 1000 THEN 5
WHEN monetary >= 500 THEN 4
WHEN monetary >= 200 THEN 3
WHEN monetary >= 50 THEN 2
ELSE 1
END AS m_score
FROM rfm_base
)
SELECT
customer_id,
r_score || f_score || m_score AS rfm_segment,
CASE
WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal Customers'
WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
WHEN r_score <= 2 AND f_score <= 2 AND m_score >= 3 THEN 'Lost High-Value'
ELSE 'Hibernating'
END AS segment_name,
recency_days,
frequency,
ROUND(monetary, 2) AS total_spent
FROM rfm_scores
ORDER BY monetary DESC;
Try it! Think about your own behavior as a customer. On a 1-5 scale, what is your R, F, and M score for Amazon? For a local coffee shop? RFM works for any business.
Real data science work combines multiple patterns. Here is a query an analyst at a SaaS company might write for a board meeting:
sql
-- Executive dashboard query: key metrics by month
WITH monthly_metrics AS (
SELECT
STRFTIME('%Y-%m', event_date) AS month,
COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END) AS new_signups,
COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END) AS paying_users,
SUM(CASE WHEN event = 'purchase' THEN amount ELSE 0 END) AS revenue,
COUNT(DISTINCT CASE WHEN event = 'churn' THEN user_id END) AS churned_users
FROM events
GROUP BY STRFTIME('%Y-%m', event_date)
)
SELECT
curr.month,
curr.new_signups,
curr.paying_users,
curr.revenue,
curr.churned_users,
-- MoM growth rates
CASE
WHEN prev.revenue > 0
THEN ROUND(100.0 * (curr.revenue - prev.revenue) / prev.revenue, 1)
END AS revenue_mom_pct,
CASE
WHEN prev.new_signups > 0
THEN ROUND(100.0 * (curr.new_signups - prev.new_signups) / prev.new_signups, 1)
END AS signups_mom_pct,
-- Net revenue retention
CASE
WHEN curr.paying_users > 0
THEN ROUND(curr.revenue / curr.paying_users, 2)
END AS arpu
FROM monthly_metrics curr
LEFT JOIN monthly_metrics prev
ON curr.month = STRFTIME('%Y-%m', DATE(prev.month || '-01', '+1 month'))
ORDER BY curr.month;
In cohort retention analysis, what does 'Period 3 retention = 28%' mean for the January 2024 cohort?
What's next: The final lesson — SQL interview patterns. Top-N per group, gaps and islands, pivots, recursive hierarchies, conditional aggregation. The exact templates that show up in 90% of FAANG SQL screens, with the muscle memory to write them under interview pressure.