Product Order Stats with Lateral — LATERAL Joins in SQL

The problem

Brightlane's inventory planning team needs to understand purchase frequency and sales volume for each product in the catalog.

Write a query to return every product's ID, name, total times ordered, and total quantity sold across every order.

Assumptions:

A product's times-ordered count is the number of line items linked to that product_id. The total quantity is the combined quantity across those line items.
Every product must appear in the result, including products that have never been ordered. Products with no line items should show a count of 0 and a missing total quantity.

Output:

One row per product, with columns id, name, times_ordered, and total_quantity.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  p.id,
  p.name,
  stats.times_ordered,
  stats.total_quantity
FROM
  products p
  CROSS JOIN LATERAL (
    SELECT
      COUNT(*) AS times_ordered,
      SUM(quantity) AS total_quantity
    FROM
      order_items oi
    WHERE
      oi.product_id = p.id
  ) stats

The shape

The lateral subquery sees p.id from the outer product row and collapses that product's line items into one stats row. With no GROUP BY inside the aggregate, the lateral always returns one row per product, which is what keeps every product, including the never-ordered ones, in the inventory planning result.

Clause by clause

SELECT p.id, p.name, stats.times_ordered, stats.total_quantity returns the product identity from the outer table and the two aggregate stats from the lateral.
FROM products p is the driving table. Every product will be paired with exactly one lateral row, so the full catalog appears in the result.
CROSS JOIN LATERAL ( SELECT COUNT(*) AS times_ordered, SUM(quantity) AS total_quantity FROM order_items oi WHERE oi.product_id = p.id ) stats runs once per product. Because the aggregate has no GROUP BY, it returns one row even when the filter matches zero line items. COUNT(*) is 0 for a never-ordered product; SUM(quantity) is NULL, which is the missing total the prompt asks for.

The trap

It is tempting to write the lateral as SELECT product_id, quantity FROM order_items WHERE oi.product_id = p.id and aggregate outside. That version returns zero rows for never-ordered products, CROSS JOIN LATERAL drops those products, and the catalog is no longer complete. Aggregating inside the lateral is what guarantees one row per product. The rule: a COUNT/SUM lateral with no GROUP BY is the shape that keeps every outer row under CROSS JOIN LATERAL.

You practiced CROSS JOIN LATERAL with an aggregate-only inner query — COUNT returns 0 and SUM returns missing for parents with zero children, so every parent record still appears in the output.

Return every product's ID, name, total times ordered, and total quantity sold across every order

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.