Verify complex SQL query correctness
An agent wrote a 40-line analytics SQL query to compute monthly active users with retention cohorts across three joined tables. It returns plausible-looking numbers and runs fast. The query uses LEFT JOINs, a window function, and a DATE_TRUNC you didn't scrutinize. The output will drive a board metric. How do you verify the numbers are right before they leave your hands — given you don't fully follow the query?
distinct_active_users(event_rows: list[str]) → list[str][["u1|2026-01-15","u2|2026-01-20","u1|2026-01-31"]]out["2026-01=2"][["u1|2026-01-15","u1|2026-01-15"]]out["2026-01=1"][["u1|2026-01-31","u1|2026-02-01"]]out["2026-01=1","2026-02=1"]Treat the AI’s output as a draft to verify, not an answer to trust. Name the specific flaw and the input that triggers it, say how you’d catch it (tests, edge cases, reading critically), and how you’d re-prompt or decompose to get it right.
Vibe & agentic: describe the solution in plain language (or narrate it) and the coach grades your approach.