Description

A line's revenue is quantity × unit_price (ignore discount_pct here). In this order:

1. Create a view v_category_revenue with category_name and revenue: the total revenue of the order lines of each category's products.
2. Create a materialized view mv_category_revenue with the same two columns, from v_category_revenue.
3. Add this order line:

Code
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct)
VALUES (2, 26, 1, 1999, 0);

4. Refresh the materialized view.
5. Add this order line:

Code
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct)
VALUES (3, 6, 2, 899, 0);

Then compare the saved revenue with the live revenue. Finish with exactly this query:

Code
SELECT v.category_name, m.revenue AS saved, v.revenue AS live
FROM v_category_revenue v
JOIN mv_category_revenue m ON m.category_name = v.category_name
WHERE m.revenue <> v.revenue
ORDER BY v.category_name;

Tables