Late Data in Python

The interview question: “Yesterday's report is done. Today a row arrives for an order placed two days ago. How do you find rows like that, and what do you do about them?” This is late data: rows that arrive after the report for their day was made. It happens all the time in real jobs: a server was down for a day, a partner sends a day late, or a file is sent again. Without a plan, the report for the order's day stays wrong for good, and nobody notices.

Every row has two days. The event day is when the order was placed: ordered_at, cast to a DATE. The arrival day is the day of the file the row came in. read_csv(…, filename = true) adds a filename column that holds each row's file path. parse_filename(path, true) keeps the last part of the path and drops the extension. For example:

parse_filename('exports/2026-06-30.csv', true)  →  2026-06-30

A row is late when its event day comes before its arrival day.

The trap: counting each row on the day its file arrived. 05-04's file has 9 rows, and one of them, order 5036, was placed on 2026-05-02 at 21:47. 05-04's report would count it, and 05-02's report never would. Rows that are sent again are late too. Order 5062 comes again in 05-07's file, now cancelled, so 05-06's paid total is wrong.

There are two fixes. The first is to re-run the affected days: partition the report by event day and recompute every day a late row belongs to, as in the incremental aggregate lesson. The second is to keep the report open: a day's report becomes final only after a set number of days. Streaming tools call that limit a watermark. Here, the latest row is 2 days late. Your code must print:

order_id | status | order_day | file_day | days_late
5036 | paid | 2026-05-02 | 2026-05-04 | 2
5040 | paid | 2026-05-04 | 2026-05-05 | 1
5062 | cancelled | 2026-05-06 | 2026-05-07 | 1
(3 rows)
report_day | late_rows
2026-05-02 | 1
2026-05-04 | 1
2026-05-06 | 1
(3 rows)

Your task: the starting code loads every file into raw_orders, all as text, with its filename. Add both days to late_orders and keep only the late rows. Then count the late rows for each report day.

Assignment
Follow the Coding Tutorial to find the late rows and list the report days they make wrong.

Hint
Name the two days in the SELECT (order_day and file_day), then compare those names in the WHERE. DuckDB lets a WHERE use a column alias from its SELECT.