A summary table refreshed on a watermark

MySQL has no materialised views, so a summary table plus a refresh strategy is the manual equivalent, and the watermark is what makes the refresh incremental.

INSERT INTO daily_sales (day, total_cents, updated_at)
SELECT DATE(placed_at), SUM(total_cents), NOW()
FROM orders
WHERE placed_at >= (SELECT COALESCE(MAX(watermark), '2010-01-01')
                    FROM summary_state WHERE name = 'daily_sales')
GROUP BY DATE(placed_at)
ON DUPLICATE KEY UPDATE
  total_cents = VALUES(total_cents), updated_at = NOW();

The watermark has to be an append-only column — a created timestamp rather than an updated one — or a row edited retrospectively is never picked up. Backdated edits are the failure case for every incremental refresh, and the honest answers are a full rebuild on a schedule or a trigger that resets the watermark. A reconciliation query comparing the summary to the source on a schedule is what turns a silent divergence into an alert.