Units sold per product

The catalogue team wants every product on one sheet, with how many units actually reached customers. Products that never sold must still be on the sheet, with a 0.

Count order_items.quantity only for orders whose status is 'delivered'. A product whose only sales were cancelled, returned or still shipping has units_sold = 0.

Return one row per product in products:

column	type	meaning
product_id	integer	the product
name	text	the product name
units_sold	integer	total delivered quantity, 0 if none

Row order doesn’t matter.

Tables: products, order_items, orders.

Approach

Aggregate delivered units per product first, then attach that to the full product list with a LEFT JOIN.

SELECT p.product_id,
       p.name,
       COALESCE(d.units, 0) AS units_sold
FROM products p
LEFT JOIN (
  SELECT oi.product_id, SUM(oi.quantity) AS units
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  WHERE o.status = 'delivered'
  GROUP BY oi.product_id
) d ON d.product_id = p.product_id;

Why it works

A LEFT JOIN keeps every left row, filling the right side with NULL when nothing matches. Any WHERE condition on a right-side column then runs on those NULLs and removes the row. So the filter has to happen before the outer join: inside the subquery, or in the ON clause.

Pre-aggregating also keeps the grain clean. The subquery has one row per product, so the outer join can’t fan out, and COALESCE turns “no delivered sales” into 0.

Under the hood

The subquery is a hash join (order_items probing a hash table of delivered order_ids) feeding a hash aggregate keyed by product_id. That result, at most 40 rows, becomes the build side of the outer join. DuckDB marks which build rows were matched; for a left join it emits every probe row from products either way, with NULLs where the probe missed.