Customer Order Stats with Lateral — Subqueries vs CTEs vs Joins in SQL

The problem

Scenario: Brightlane's customer analytics team needs each customer paired with two metrics drawn from their orders: how many orders they have placed and the combined order amount. Customers with no orders should still appear in the report.

Task: Write a query to return each customer's id, their order_count, and their total_value — the combined total_amount across their orders.

Assumptions:

The result covers every customer.
A customer with no orders on record appears with order_count of 0 and total_value reported as a missing value.

Output:

One row per customer.
Columns in this order: customer_id, order_count, total_value.

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
  c.id AS customer_id,
  order_stats.order_count,
  order_stats.total_value
FROM
  customers c
  LEFT JOIN LATERAL (
    SELECT
      COUNT(*) AS order_count,
      SUM(total_amount) AS total_value
    FROM
      orders o
    WHERE
      o.customer_id = c.id
  ) AS order_stats ON TRUE

The shape

LEFT JOIN LATERAL runs a small aggregate query once per customer row, with the outer customer's id available inside it. Each customer gets a single row of paired metrics back, and the LEFT half keeps customers whose lateral query returned nothing.

Clause by clause

FROM customers c is the driving table; every customer is preserved.
LEFT JOIN LATERAL (SELECT COUNT(*) AS order_count, SUM(total_amount) AS total_value FROM orders o WHERE o.customer_id = c.id) AS order_stats ON true runs once per customer. The inner query filters orders to that customer and aggregates both metrics in a single pass. The ON true is the standard LATERAL pairing — every outer row keeps its lateral subquery's row.
SELECT c.id AS customer_id, order_stats.order_count, order_stats.total_value reads the customer's id alongside both aggregates.

Why this and not two correlated scalar subqueries

You could write (SELECT COUNT(*) ...) and (SELECT SUM(total_amount) ...) as two scalar subqueries in the SELECT list. Both are correct, but each scalar subquery walks orders separately. LATERAL computes both metrics in one pass per customer, which is the right move whenever the per-customer step needs to return more than one value.

The trap

COUNT(*) on an empty filter returns 0, but SUM(total_amount) on an empty filter returns NULL. That's the contract the prompt asks for: order_count is 0 and total_value is NULL for a customer with no orders. The two aggregates behave differently on empty input by design, and the prompt's two specs line up with that behavior exactly.

You practiced returning multiple per-customer metrics from a single LATERAL subquery — a shape that delivers multi-column output that a scalar correlated subquery cannot.

Return each customer's `id`, their `order_count`, and their `total_value` — the combined `total_amount` across their `orders`

The shape

Clause by clause

Why this and not two correlated scalar subqueries

The trap

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