SQL Analytics Case Study
▶ Data-layer diagram — self-contained HTML, opens in a browser — no install, no build
Situation
A take-home format: prove SQL skill on product tasks when there is no production data, and textbook exercises do not demonstrate systems thinking. 25 end-to-end cases on a synthetic product dataset plus one real-data case on UCI Online Retail II.
Each case is one self-contained .sql file with the question and approach in a leading comment. No server, no credentials — a single command builds the data and a DuckDB database. Plus a dbt layer (staging → marts, 18 tests) on the same database.
Task
I needed to demonstrate end-to-end SQL thinking without production data, so I owned the self-contained case files, the single-command data and database build, and the dbt layer that models them.
Actions
Data model (synthetic, seed=42, deterministic):
| Entity | Volume | Fields |
|---|---|---|
| Users | 20,000 signups (Jan–Jun 2024) | channel, country, device, ab_variant |
| Events | ~183k funnel events | app_open → view_item → add_to_cart → checkout → purchase, ~80k sessions |
| Orders | 928 purchases | amount, product category |
| Subscriptions | 268 conversions | monthly / annual plans |
| Cancellations (seed=43) | 98 | subscription_cancellations |
| Refunds (seed=43) | 53 | refunds |
| Online Retail II (real) | 1,067,371 rows | UCI dataset 502, CC BY 4.0 — case 26 |
Schema: data/schema.sql. Generator: data/generate_data.py. Engagement decays geometrically from signup; retention is weighted by acquisition channel. Additive tables (cases 21–25) are generated on a separate RNG stream (seed=43) so the seed-42 numbers in cases 1–20 never move. The real-data table (case 26) is loaded from a committed parquet (data/realdata/) into the same database.
25 cases:
| # | Case | Technique |
|---|---|---|
| 01 | Funnel conversion | cumulative counts, LAG / FIRST_VALUE |
| 02 | N-day retention by cohort | DATE_TRUNC('month', signup_date) |
| 03 | Rolling 30-day retention | EXISTS subqueries per window |
| 04 | DAU / MAU / stickiness | trailing-28d range join |
| 05 | LTV by cohort | left join + COALESCE for zero-revenue |
| 06 | Top-N categories per country | ROW_NUMBER() OVER (PARTITION BY ...) |
| 07 | Cumulative revenue | SUM() ... UNBOUNDED PRECEDING |
| 08 | Longest active-day streak | gaps-and-islands (row_number → island key) |
| 09 | A/B conversion by variant | in-SQL z-test + p-value (Abramowitz–Stegun) |
| 10 | Revenue attribution | first-touch vs lifetime, correlated subquery |
| 11 | 7-day moving average of DAU | AVG() OVER (... ROWS BETWEEN 6 PRECEDING ...) |
| 12 | Top-2 revenue users per country | QUALIFY |
| 13 | Monthly revenue by category | PIVOT long → wide |
| 14 | Subscription MRR | recursive CTE (billing rows per subscription) |
| 15 | Order amount distribution | MEDIAN, QUANTILE_CONT (p90/p99) |
| 16 | Sessionization + session depth | gaps-and-islands on timestamps, validation vs ground truth |
| 17 | Weekly lifecycle (new/returning/resurrecting/dormant) | state transitions, LAG/LEAD |
| 18 | Cohort revenue retention (triangle) | months-since-signup, % of period-0 |
| 19 | Repeat purchase & time between orders | LAG within user |
| 20 | RFM segmentation | NTILE quintiles, segment-score rules |
| 21 | Subscription churn (logo & MRR) | monthly churn, FILTER aggregates |
| 22 | Refunds & net revenue | left join, gross-vs-net |
| 23 | Pareto / revenue concentration | NTILE(10), cumulative-share curve |
| 24 | Daily revenue anomaly detection | robust MAD z-score, rolling baseline |
| 25 | Purchase → subscription conversion | join to subscriptions, time-to-convert |
| 26 | Real data — repeat purchase & concentration | invoice→customer rollup, order-count buckets, revenue shares |
Quick start
uv run python data/generate_data.py # data/analytics.duckdb (incl. real-data table)
uv run python run.py # list cases
uv run python run.py 1 # run case 1
uv run python run.py 26 # real-data case
uv run --extra dev pytest -q # 43 regression tests
uv run python scripts/report.py # reports/index.html
cd dbt && uv run dbt build --profiles-dir . # dbt: models + 18 testsThe runner prints the case question, executes the SQL against data/analytics.duckdb, and renders the result as a table. The charted report is published to GitHub Pages automatically on every push.
Result
Each case covers a specific window-function pattern, and the findings are honest rather than engineered. Three teaser signals (numbers pinned to cases.md):
- the funnel drops 78% at add-to-cart → checkout (54% is the view→cart step);
- retention falls from ~19.5% (D1) to ~4% (D30) — the leak is the onboarding window;
- only 3.5% of buyers repeat (896 buyers, 31 repeat) — a one-and-done purchase engine.
Also:
- RFM degenerates into a recency story; the top decile delivers only 22% of revenue (no whales);
- logo churn climbs to ~15%/month even as MRR compounds;
- sessionization reproduces 80k pre-assigned sessions at 99.6% fidelity;
- real data flips the conclusion: on UCI Online Retail II, 72.4% of customers repeat and customers with 11+ orders (14.9% of the base) drive 65% of revenue — same SQL, opposite business answer.
Splitting question and SQL in one file plus regression invariants makes the cases self-checking: 43 pytest tests and 18 dbt tests keep cases.md and the code in sync, and dbt build is green in CI.
Limitations
25 of 26 cases are synthetic: their numbers describe the shape of generated data, not a real product’s behavior. Case 26 on UCI Online Retail II shows how far the conclusion can flip on real data (72.4% repeat vs 3.5%) — that is the honest boundary of applicability: the SQL patterns transfer, the specific metrics do not.
Documentation
Frequently asked
What data do the cases run on?
25 cases on a synthetic dataset (~183k events, seed=42 plus an additive seed=43) and 1 real-data case on UCI Online Retail II.
Where does the funnel lose the most?
At add-to-cart → checkout: 78% drop off (54% is the view→cart step).
What does retention look like?
Retention falls from ~19.5% on D1 to ~4% on D30 and only 3.5% repeat; on the real dataset 72.4% return.
What does the dbt layer add?
A staging → marts model layer on DuckDB, 18 dbt tests, and a regression test with deterministic invariants per case.