Dedupe, Keeping the Latest

The interviewer asks: "The same order arrives several times; keep the newest version." The daily exports in orders/ re-send orders: 5027 is in 05-03's file twice, 5040 is in both 05-04's and 05-05's files, and 5062 is paid in 05-06's file but cancelled in 05-07's. Every report needs one row per order: a duplicate counts an order's revenue twice, and an old copy keeps a status that is no longer true.

The trap: SELECT DISTINCT * on the rows without the file name. DISTINCT removes only rows equal in every column, so 5027's and 5040's copies collapse but 5062's paid and cancelled copies both stay: 76 rows of 78, with 5062 still there twice.

The right answer: number each order's copies and keep number 1. ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) restarts the numbering at 1 for each value of the PARTITION BY column and counts in the ORDER BY's order; QUALIFY keeps the rows whose window value passes the test. The latest price per product, on another table:

query = (
    "SELECT * "
    "FROM price_changes "
    "QUALIFY ROW_NUMBER() OVER (PARTITION BY product ORDER BY changed_at DESC) = 1 "
)

The exports have no updated-at column, so the file is the version. raw_orders holds all 78 rows of orders/*.csv, read with filename = true (a filename column such as orders/2026-05-07.csv) and all_varchar = true. The names hold the day as year-month-day, so the newest file has the biggest name. At work you'd order by an updated-at time or the load time when the rows carry one. The check counts the rows and shows the three repeated orders:

rows | orders
75 | 75
(1 row)
order_id | status | filename
5027 | paid | orders/2026-05-03.csv
5040 | paid | orders/2026-05-05.csv
5062 | cancelled | orders/2026-05-07.csv
(3 rows)

Your task: below FROM raw_orders, keep one row per order_id: the copy from the newest file.

Assignment
Follow the Coding Tutorial to keep only the newest copy of each order with QUALIFY ROW_NUMBER().

Hint
Type the QUALIFY ROW_NUMBER() OVER ( line and its closing ) = 1 first, then the two lines between them, one string piece each. DESC puts the newest file first, so its copy is number 1.