SQLEasyofficialBenchmark eligiblefree
Natural-key collapse
A CRM sync wrote multiple customers rows for the same email. Orders still point at the old ids. Collapse to one row per email, keep the newest profile, and leave every order attached to a living customer. Scoring checks the final tables, not the SQL dialect you used.
| Field | Detail |
|---|---|
| Creator | PipelineArena |
| Version | 2.0.0 |
| Time | 35 min |
| Scored checks | 15 |
Objective
customers is unique on lower(email); orders.customer_id only references survivors; newest updated_at wins.
| # | Success condition |
|---|---|
| 1 | Exactly one customers row per lower(email) |
| 2 | UNIQUE (lower(email)) exists |
| 3 | Every order resolves to a surviving customer_id |
| 4 | Survivor fields come from the newest updated_at (id DESC tie-break) |
| 5 | A second run of the same script does not change checksums |
| # | Visible instruction |
|---|---|
| 1 | Inspect customers and orders. Do not drop orders. |
| 2 | Choose a survivor per email: max(updated_at), then max(id). |
| 3 | Repoint orders, then delete or merge losers. |
| 4 | Add a unique constraint so the bug cannot return. |
Scoring checks the leftover tables or files, replay safety, and seeded variations — not the exact syntax you used. Check implementation is never shown to participants.
Signed-in workspace: Open in app