Project: a daily order mart
Priya needs a repeatable order-reporting script for finance: a daily mart at date/channel grain and a monthly view over it. Exclude processing orders, retain a refund count, and report recorded basket value separately from shipping. Deliver both writable objects in practice with an explicit physical design.
Priya, Lumen's reporting owner, needs a daily order mart and a monthly view for the finance team. She wants one row per order_date and channel, with order count, refund count, merchandise value and shipping charges. Processing orders are excluded because they haven't reached a settled operational state in this extract.
Calculate merchandise from quantity times unit_price inside each order's items. Keep shipping at order grain so it isn't repeated by an item expansion. Refunds remain in the mart and have their own count. The merchandise measure is the recorded basket value across included orders; it isn't net recognized revenue, because the fixture doesn't provide refund amounts or accounting recognition rules.
Build the report in stages, then save it as a table partitioned by date and clustered by channel. Finish with a monthly view over that table. Each step carries your worksheet forward, so the final script can rebuild both reporting objects from source.
WITH order_values AS (SELECT order_date, channel, status, COALESCE((SELECT SUM(item.qty * item.unit_price) FROM UNNEST(items) AS item), 0) AS merchandise_value, shipping.cost AS shipping_cost FROM shop.orders WHERE status != 'processing') SELECT order_date, channel, COUNT(*) AS orders, COUNTIF(status = 'refunded') AS refunded, ROUND(SUM(merchandise_value), 2) AS merchandise_value, ROUND(SUM(shipping_cost), 2) AS shipping_cost FROM order_values GROUP BY order_date, channel;
Order-grain values feed a day-and-channel summary without multiplying shipping.