Explode the array so each item becomes its own row, then group by product. The syntax depends on the engine: UNNEST in BigQuery, LATERAL FLATTEN in Snowflake, explode in Spark. The important part is that the grain of the result changes from one row per order to one row per order item.
Query in BigQuery
SELECT item.product_id,
SUM(item.quantity * item.unit_price) AS revenue
FROM orders o
LEFT JOIN UNNEST(o.items) AS item
GROUP BY item.product_id;Same idea elsewhere
-- Snowflake
SELECT f.value:product_id::STRING AS product_id,
SUM(f.value:quantity::NUMBER * f.value:unit_price::NUMBER) AS revenue
FROM orders o,
LATERAL FLATTEN(input => o.items, OUTER => TRUE) f
GROUP BY 1;In Spark, explode(items) creates one row per element, and explode_outer keeps rows with empty arrays.
Empty arrays
A plain CROSS JOIN UNNEST (or the comma form) drops orders whose array is empty or NULL, because there is nothing to pair with. For revenue per product that is harmless. For "how many orders did we receive", it quietly undercounts. Use LEFT JOIN UNNEST (or OUTER => TRUE, or explode_outer) if those orders must stay.
The double counting trap
order_id order_total item 1 100 pen 1 100 book
After the explode, order_total is repeated on every item row. SUM(order_total) now gives 200 for an order worth 100. Order-level columns do not belong to the item grain. Either compute order-level sums before exploding, or use the item-level amounts (quantity * unit_price) for anything after it. Counting orders needs COUNT(DISTINCT order_id).
What to say in the interview
Mention the grain change in one sentence, and mention what happens to empty arrays. Those two points show you know the pitfalls. For heavy repeated use, a permanent order_items table, created once by flattening the array during loading, is cleaner than exploding on every query.