Deviopath › PostgreSQL

Materialized Daily Revenue

Hard · Views

A materialized view runs its query once and stores the result, so reading it is fast. The catch: it does not change when the tables change. REFRESH MATERIALIZED VIEW name; runs the query again and replaces the stored rows.

1. Create a materialized view daily_revenue with columns day (the date of ordered_at), order_count and revenue (sum of amount), one row per day, ignoring 'cancelled' orders. 2. A late order arrives: insert order 100 for customer 1 at 2026-03-02 18:00, amount 40.00, status 'paid'. 3. Refresh the view so it includes that order.

The check adds another order and reads the view before and after its own refresh.

Next: Upsert Page Counters