Analytical Patterns
Common patterns used in data analysis and reporting.
Questions this answers
- →How do I calculate month-over-month growth?
- →How do I build a cohort analysis?
- →How do I find the first event per user?
- →How do I calculate a retention rate?
How it works
Data analysis in SQL comes down to a handful of patterns used repeatedly. Once you recognize them, you can answer most reporting questions quickly. These patterns combine JOINs, window functions, and CTEs in specific ways.
Month-over-month growth
WITH monthly AS (
SELECT strftime('%Y-%m', created_at) AS month,
COUNT(*) AS signups
FROM users GROUP BY month
)
SELECT month, signups,
LAG(signups) OVER (ORDER BY month) AS prev,
ROUND(100.0 * (signups - LAG(signups) OVER (ORDER BY month))
/ LAG(signups) OVER (ORDER BY month), 1) AS pct_change
FROM monthly;First event per user
SELECT user_id, MIN(created_at) AS first_order FROM orders GROUP BY user_id;
💡 Or use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) = 1 for the full row.
Ready to practise Analytical Patterns?
Solve real exercises with AI feedback — free to start.
Try it free →