The setting

Cadenza Tickets is the online box office for 38 independent music venues across the Midwest. The analytics warehouse runs on Postgres, and every team from marketing to finance comes to you, the analytics engineer, for numbers they can trust.

Every mission on this path happens at the same company, so context carries over the way it does in a real job: the data you cleaned in mission two is the data the finance lead questions in mission four.

The missions

1. select and filterstarter

Answer marketing's three pulls from the Cadenza orders table

You are the analytics engineer at Cadenza Tickets. Joelle, the marketing lead, needs three data pulls from the orders table before her Thursday campaign review, and the last person who tried this cut off the March 9 evening orders with a sloppy date filter.

You deliver: Three SQL queries answering Joelle's requests, each with a filter-by-filter explanation and the sample rows it would return.

Scored on: Correct filters, Date boundary handling, NULL promo handling, Clear explanations.

Working from: orders_schema.md, query_requests.md.

2. joinsstarter

Join March event revenue to venues without losing the orphans

Finance wants March gross revenue rolled up by venue city for the quarterly close. The catch: two legacy venues were deleted from the venue CRM in the February cleanup, and their March events are still in the warehouse pointing at venue ids that no longer exist.

You deliver: A city revenue rollup query plus an orphans note naming the unmatched events, the join choice, and the revenue at stake.

Scored on: Join choice and reasoning, Orphans identified, City rollup logic, Durable fix suggested.

Working from: venues_reference.md, march_event_revenue.csv.

3. aggregationscore

Rebuild the March revenue rollup after the double-loaded batch

Finance flagged that Cadenza's March revenue report looks high, and the load note explains why: batch B-0312 was accidentally loaded twice into raw_orders, once as B-0312 and again as B-0312R. Nobody has cleaned it up, and the monthly close is tomorrow.

You deliver: Aggregation queries over the dirty orders table plus a reconciliation of correct versus inflated March revenue.

Scored on: Dedupe handling, Correct totals, Refund treatment, Grouping and HAVING.

Working from: load_note.md, raw_orders_march.csv.

4. window functionscore

Rank Cadenza's spring on-sale week with window functions

The programming team is picking which spring events get extra ad spend, and they want rankings and momentum, not just totals. Daily sales for the four biggest on-sale events are in the warehouse, and there is a genuine tie waiting to bite whoever ranks carelessly.

You deliver: Three window function queries with explanations, an explicit tie-handling decision, and a reading of E-2210's day-over-day pattern.

Scored on: Window construction, Tie handling decision, LAG analysis, Explained windows.

Working from: analytics_requests.md, daily_event_sales.csv.

5. data quality checksstretch

Write the SQL data quality suite after three bad Fridays

Three Fridays in a row, a data incident reached Cadenza's stakeholders before anyone in the data team knew: an inflated revenue email, a broken city dashboard, and impossible order timestamps from the partner channel. The head of data has asked you for a standing data quality suite so the fourth Friday is boring.

You deliver: A SQL data quality suite: concrete violation-returning queries with severity, meaning, and on-call action, mapped to the three incidents.

Scored on: Covers all three incidents, Runnable violation queries, Structural breadth, Operational detail.

Working from: dq_incidents.md, warehouse_reference.md.

How the scoring works

Each deliverable is graded against the rubric written for that mission. Separately, every mission on every path is graded on how you used AI, against the same four criteria:

  • Understood the task. The learner framed the goal for the assistant clearly instead of pasting the brief and hoping.
  • Grounded in the material. The learner directed the assistant into the provided files and based the work on them, not on invented facts.
  • Verified the output. The learner checked claims, numbers, or coverage against the source material before submitting.
  • Iterated with judgment. The learner refined weak parts of the draft with specific follow-ups rather than accepting the first answer.

Both scores, with the work behind them, go on your proof profile. That is what makes a claim like "I can use AI for sql and analytics" something an employer can check.