Analytics SQL Correctness Review

Review this analytical SQL for correctness — not performance. The failure mode we are hunting: the query runs fine, returns plausible-looking numbers, and those numbers are wrong. Nobody notices for a quarter. Question the query answers (in plain English): {{question}} Schema (tables, keys, grain of each table): {{schema}} Query: ```sql {{query}} ``` Check systematically: 1. **Grain & fan-out** — For every join: what is the cardinality (1:1, 1:N, N:M)? Any 1:N join before an aggregation silently multiplies the "1" side's measures. Verify every SUM/COUNT aggregates at the grain it thinks it does. This is the #1 analytics bug — check it first and show the grain of each intermediate result. 2. **NULL traps** — `NOT IN` with a subquery that can return NULL (returns zero rows), `<>` filters silently dropping NULL rows that belong in the denominator, COUNT(col) vs COUNT(*) mismatches, NULLs collapsing in GROUP BY. 3. **Ratio & average abuse** — averages of averages instead of weighted averages, ratios aggregated as AVG(a/b) vs SUM(a)/SUM(b), percentages whose numerator and denominator use different filters. 4. **Time bugs** — date truncation in the wrong timezone (UTC midnight is not business midnight), BETWEEN on timestamps including/excluding the boundary day, incomplete current-period rows compared against complete past periods, DST-day double/missing hours. 5. **Window frames** — default RANGE frames with ties behaving unexpectedly, ROW_NUMBER vs RANK on ties, PARTITION BY missing a key. 6. **Filter placement** — conditions on the right side of a LEFT JOIN placed in WHERE (silently converts to INNER), filters applied after aggregation that should be before, or vice versa. For each issue found, provide: - The offending fragment, quoted - **A minimal counter-example**: a tiny concrete dataset (3–6 rows, shown as a table) where the query provably returns the wrong answer, with the wrong and the right number side by side - The corrected SQL fragment If the query is correct, state the two riskiest constructs you checked and the reasoning that cleared them. Close with the corrected full query if any changes were needed.