Remove Duplicates

Daily exports repeat themselves. In raw_orders, 78 rows hold only 75 orders: order 5027 appears twice in 05-03's file (the same row), order 5040 was sent again a day later in 05-05's file, and order 5062 was sent again in 05-07's file, now cancelled (05-06's file has it paid). Every SUM and COUNT counts a copy again, so revenue comes out too high, and a stale copy keeps old news. Nothing fails, the numbers are just wrong: that's why a data engineer removes duplicates while building the clean table.

You know ROW_NUMBER() from the SQL path: it numbers rows 1, 2, 3… in the window its OVER ( … ) describes. PARTITION BY starts the numbering again at 1 for each group, and the ORDER BY inside OVER decides which row of a group gets the 1. QUALIFY is new: a DuckDB clause that filters on a window function the way WHERE filters on a column, so you don't need a subquery. Here it keeps the newest price of each product in a prices table:

SELECT product, price, changed_on
FROM prices
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY product
    ORDER BY changed_on DESC
) = 1

The file names start with the day (orders/2026-05-07.csv), so ORDER BY filename DESC gives the copy from the newest file the number 1. Leave the ORDER BY out and each order still keeps one row, but no rule says which copy: you may keep the old news.

The check counts the rows and lists the three orders that came twice. The starting code prints 78 rows in stg_orders and six rows for those orders; your code must print:

raw_rows | stg_rows | order_ids
78 | 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: the starting code loads raw_orders and builds stg_orders from it with typed columns, but keeps all 78 rows. Below FROM raw_orders, add a QUALIFY that keeps, for each order_id, only the copy from the newest file.

Assignment
Follow the Coding Tutorial to keep one row per order in stg_orders: the copy from the newest file.

Hint
Type the QUALIFY ROW_NUMBER() OVER ( line and the ) = 1 line first, then PARTITION BY order_id and ORDER BY filename DESC between them. Each one is its own string piece that ends with a space.