An Incremental Aggregate in Python

The interview question: “daily_revenue holds one total per day. A new day's orders arrive. How do you update it without rebuilding the whole table?” The answer is an incremental aggregate: recompute only the days the new rows touch. First you DELETE those days from the summary. Then you INSERT their fresh totals, summed again from all the orders of those days. A data engineer needs this because a full rebuild re-reads every order ever made, every night. The job gets slower each day until it no longer finishes in time.

A small example on another table: when new lines for the Pen arrive, a per-product summary redoes only the Pen.

DELETE FROM product_units WHERE product = 'Pen';
INSERT INTO product_units
SELECT product, SUM(quantity) FROM stg_lines
WHERE product = 'Pen' GROUP BY product;

The trap: most people recompute only the run date, 2026-05-04. But 05-04's file also holds order 5036, which was placed on 2026-05-02 at 21:47 and arrived late. If you recompute only 05-04, the 05-02 total stays at 382 when it should be 384. The days to redo come from the new rows themselves: SELECT order_day FROM new_orders gives 2026-05-02 and 2026-05-04. An INSERT with no DELETE before it also leaves two rows for each of those days.

The starting code builds stg_orders from the files of 05-01 to 05-03, and builds daily_revenue from it on 05-03. Then it loads 05-04's file into new_orders and adds those rows to stg_orders. The INSERT is already written. Its ? puts run_date in updated_on. In a real job you'd run the DELETE and the INSERT in one transaction, as in the idempotent load. Your code must print:

day | revenue | updated_on
2026-05-01 | 274 | 2026-05-03
2026-05-02 | 384 | 2026-05-04
2026-05-03 | 245 | 2026-05-03
2026-05-04 | 122 | 2026-05-04
(4 rows)
Same as a full rebuild: True

05-01 and 05-03 keep updated_on 2026-05-03: the job never touched them.

Your task: write a DELETE that removes from daily_revenue the days that new_orders has orders for. Run it, then limit the INSERT to the same days.

Assignment
Follow the Coding Tutorial to delete the touched days from daily_revenue and insert only their new totals.

Hint
Take the days from the new rows, not from run_date: use a subquery that selects order_day from new_orders. The SUM still reads stg_orders, so a recomputed day counts its old orders as well as the new ones.