Reporting handed me an events table with duplicate rows per entity (late-arriving data, at-least-once delivery) and I needed exactly the latest row per key. The naive GROUP BY + MAX(updated_at) approach fell apart when two rows shared the same timestamp, and a DISTINCT silently kept the wrong columns.
My prompt now forces ROW_NUMBER() with an explicit, total-ordering tiebreaker and requires the model to justify why the result is exactly one row per key. Making it prove the ordering is total (no ties possible) is what stopped the flaky output.
Anyone have a cleaner pattern than QUALIFY for warehouses that don't support it? I fall back to a CTE + WHERE rn = 1.
Write a SQL query (dialect: {DIALECT}) that returns exactly one row per {KEY_COLS} from table `{TABLE}`: the latest record by {ORDER_COL}.
Requirements:
- Use `ROW_NUMBER() OVER (PARTITION BY {KEY_COLS} ORDER BY ...)`. The ORDER BY must define a TOTAL order so no two rows can tie: primary key `{ORDER_COL} DESC`, then a deterministic tiebreaker (e.g. `id DESC`). Explain why ties are impossible.
- Do NOT use `SELECT DISTINCT` or `GROUP BY + MAX` for this (explain in one line why they're wrong here: DISTINCT can't pick a full row, MAX ignores tiebreakers).
- Structure as a CTE (`ranked`) then `WHERE rn = 1` so it runs on warehouses without `QUALIFY`; if {DIALECT} supports QUALIFY, also show that shorter form.
- Filter out soft-deleted rows if `{SOFT_DELETE_COL}` applies.
- Add a `-- SELF-CHECK` query that asserts the result is truly one-row-per-key: e.g. `SELECT {KEY_COLS}, COUNT(*) c FROM result GROUP BY {KEY_COLS} HAVING c > 1` must return zero rows. State the expected result.
- Note which columns should be indexed to make the window efficient.
Be explicit about NULL handling in the ORDER BY (NULLS FIRST/LAST) and why you chose it.