Snapshots and Events

A status changes over time: an order is placed, paid, shipped, delivered. A warehouse can keep that history in two shapes. An event table has one row per change (order_id, status, changed_at): it grows only when something happens and keeps the exact time of every change. A snapshot table has one row per order per day with the status at the end of that day: easy to read, but it grows every day for every order, and two changes on the same day show up as one.

Data engineers need history because people ask about the past: how many orders were paid but not yet shipped at the end of May 3? A table that only holds today's status can't answer that. From events you can rebuild the status at any moment: keep the events up to that moment, then take each one's latest. For example, with price events for one product, the Mug's price on 2026-04-15 is 12, the latest event at or before that day:

product | price | changed_at
Mug     | 12    | 2026-03-01
Mug     | 14    | 2026-06-01

To find each order's latest event, number its events with the window function ROW_NUMBER() OVER (PARTITION BY … ORDER BY … DESC): PARTITION BY starts the count again at 1 for every order, and DESC gives number 1 to the newest event. A WHERE inside the CTE runs before the numbering, so it decides which events can be number 1.

The hidden setup writes two small files. order_events.csv holds 14 status changes of orders 5011 to 5014:

order_id | status | changed_at
5011 | placed | 2026-05-02 09:55:00
5011 | cancelled | 2026-05-02 10:40:00
5012 | placed | 2026-05-02 10:06:00
5012 | paid | 2026-05-02 10:07:00
5012 | shipped | 2026-05-03 09:30:00
5012 | delivered | 2026-05-05 12:00:00
5013 | placed | 2026-05-02 12:01:00
5013 | paid | 2026-05-02 12:02:00
5013 | shipped | 2026-05-03 08:15:00
5013 | delivered | 2026-05-03 18:40:00
5014 | placed | 2026-05-02 12:18:00
5014 | paid | 2026-05-02 12:19:00
5014 | shipped | 2026-05-04 07:45:00
5014 | delivered | 2026-05-06 11:00:00

order_status_daily.csv is the daily snapshot built from them: snapshot_date, order_id, status for the 4 orders on each day from May 2 to May 6. The starting code holds the shape of query: a CTE named ranked and a main query that reads it. The check prints your query, the snapshot's rows for 2026-05-03, and both files' row counts:

order_id | status | changed_at
5011 | cancelled | 2026-05-02 10:40:00
5012 | shipped | 2026-05-03 09:30:00
5013 | delivered | 2026-05-03 18:40:00
5014 | paid | 2026-05-02 12:19:00
(4 rows)
From the daily snapshot:
order_id | status
5011 | cancelled
5012 | shipped
5013 | delivered
5014 | paid
(4 rows)
event_rows | snapshot_rows
14 | 20
(1 row)

Your task: inside ranked, read the events, number each order's events newest first as row_num, and keep only the events before '2026-05-04'; in the main query keep row_num = 1. Your four statuses must match the snapshot's.

Assignment
Follow the Coding Tutorial to rebuild every order's status at the end of May 3 from the events, and compare it with the daily snapshot.

Hint
'2026-05-04' is midnight as May 4 starts, so changed_at < '2026-05-04' keeps everything up to the end of May 3. Filter inside ranked, before the numbering: filtering next to row_num = 1 would drop the orders whose latest event came later.