Brightlane's fulfillment director wants to evaluate order complexity across the customer base.
Write a query to return one row per order with at least one line item, showing the customer ID, customer name, order ID, and the count of line items in that order.
Assumptions:
- Each order's item count is the number of line items linked to that order. Orders with zero line items do not appear in the result, and customers with no qualifying orders also do not appear.
Output:
- One row per order with at least one line item, with columns
customer_id,name,order_id, anditem_count.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
c.id AS customer_id,
c.name,
o_data.order_id,
o_data.item_count
FROM
customers c
CROSS JOIN LATERAL (
SELECT
o.id AS order_id,
COUNT(*) AS item_count
FROM
orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE
o.customer_id = c.id
GROUP BY
o.id
) o_dataThe shape
The lateral runs per customer and groups that customer's orders, returning one row per qualifying order with its line-item count. Because the lateral returns multiple rows when a customer has multiple orders, the outer customer row is duplicated once per order, which is the per-order shape the fulfillment director asked for.
Clause by clause
SELECT c.id AS customer_id, c.name, o_data.order_id, o_data.item_countreturns the customer's identity from the outer table and the per-order results from the lateral.FROM customers cis the driving table; the lateral is evaluated once per customer row.CROSS JOIN LATERAL ( ... ) o_datais the lateral subquery. Inside it,FROM orders o JOIN order_items oi ON oi.order_id = o.idpairs each of this customer's orders with its line items,WHERE o.customer_id = c.idis the correlated filter that scopes the join to the outer customer,GROUP BY o.idcollapses to one row per order, andSELECT o.id AS order_id, COUNT(*) AS item_countreturns the count of items per order. Because the inner join drops orders with zero line items and the lateral inherits that, customers with no qualifying orders return zero rows and are dropped by the outerCROSS JOIN.
Why this and not a flat three-table join
FROM customers c JOIN orders o ON ... JOIN order_items oi ON ... GROUP BY c.id, c.name, o.id returns the same rows on this data and is a fine answer to this exact prompt. The lateral form is more direct because it expresses the question as it was asked: "for each customer, summarise each of their orders." When the per-order computation grows beyond a simple count (a per-order ORDER BY ... LIMIT over items, a per-order CASE rollup), the flat join needs a derived table to hold the per-order result; the lateral already has it.
The trap
The WHERE o.customer_id = c.id is what makes the subquery lateral. Drop it and PostgreSQL refuses to compile the query because the inner SELECT references c.id from a sibling FROM item, which is exactly the access LATERAL exists to grant. Every column inside the lateral that comes from the outer customer must be reachable through that correlated filter.
You practiced CROSS JOIN LATERAL over a multi-table inner query that aggregates per group — the lateral returns multiple rows (one per qualifying order) and drops outer records whose lateral set is empty.
