Brief
Write 5 correct queries against the messy orders CSV (broken joins, duplicate
keys, null customer ids): (1) top-2 orders per customer, (2) days-since-last-order
with >30-day flags, (3) monthly running revenue, (4) longest active streak per
user (gaps-islands), (5) CTE refactor of one nested query.
Dataset
orders CSV + train/holdout split. Train shows expected row counts; holdout is
hidden and used for grading. Quarantine rules for null keys are part of the score.
Starter guardrails (88)
- Use the provided skeleton with 5 labeled query slots; one query per slot.
- CTEs only for nesting deeper than 2 levels; explicit
ROWSframes on all running aggregates; noSELECT *in final outputs.
Deterministic checks
All 5 queries execute without error; row counts match train; holdout exact-match on queries 1–3 within tolerance on 4–5.
100-pt rubric
- Correctness per query (5 x 12 pts): 60
- CTE readability + explicit frames (15 pts)
- Null-key quarantine handling (10 pts)
- Deterministic holdout match (10 pts)
- One-paragraph method note (5 pts)