Capstone Audit a Dashboard Query

The one idea: A query that runs without errors and returns tidy numbers can still be completely wrong. Your job isn't to rewrite it — it's to read it, find where the logic quietly breaks, and describe the fix clearly enough that a person or an AI can make it.

⚠️ The trap: I spot-checked one number on the dashboard and it looked reasonable, so the whole query is sound. That feels right and it's wrong. **A plausible number is the disguise, not the proof — audit each CTE by its grain (what is one row?), because a fan-out join, a BETWEEN date cutoff, and COUNT(*)-as-users each return a believable-but-wrong figure with no error.**

This is the final exam, worked in front of you. One realistic scenario, one complete query, and the full audit — the same methodology you'll use the first time a real dashboard lands on your desk with a number that decides something. The query below contains three bugs. All three come from earlier lessons. None of them produce an error message. Read the query and form your own suspicions before the walkthrough — then compare.

The scenario

Your company's Weekly Executive Dashboard — Revenue & Retention shows, for the four weeks of June 2026 (Monday June 1 through Sunday June 28): weekly revenue, weekly active users, and revenue per active user. An AI wrote the query from this schema:

• orders(order_id, customer_id, order_total, created_at timestamptz) — one row per checkout
• order_items(item_id, order_id, product_id, quantity) — one row per product within a checkout
• sessions(session_id, customer_id, started_at timestamptz) — one row per visit

The AI's query, with…