Customer Order Stats with Lateral — LATERAL Joins in SQL

The problem

Brightlane's CRM team is building a customer engagement dashboard and needs order volume and revenue figures for every customer in a single result set.

Write a query to return every customer's ID, name, total number of orders placed, and total amount spent across every order.

Assumptions:

A customer's order count is the number of orders linked to that customer_id. The total spend is the combined total_amount across those orders.
Every customer must appear in the result. Customers with no orders on record should show an order count of 0 and a missing total spend.

Output:

One row per customer, with columns id, name, order_count, and total_spent.

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,
  c.name,
  stats.order_count,
  stats.total_spent
FROM
  customers c
  CROSS JOIN LATERAL (
    SELECT
      COUNT(*) AS order_count,
      SUM(total_amount) AS total_spent
    FROM
      orders o
    WHERE
      o.customer_id = c.id
  ) stats

The shape

The lateral subquery sees c.id from the outer customer row and reduces that customer's orders to one row of stats. CROSS JOIN LATERAL makes that per-row execution legal; the aggregate without GROUP BY collapses each customer's orders into a single order_count plus total_spent, so every customer in the engagement dashboard contributes exactly one output row.

Clause by clause

SELECT c.id, c.name, stats.order_count, stats.total_spent returns the four columns the dashboard asks for, pulling the first two from the outer customer and the last two from the lateral result.
FROM customers c is the driving table. Every row here will be paired with one row from the lateral, so every customer is preserved in the result.
CROSS JOIN LATERAL ( SELECT COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM orders o WHERE o.customer_id = c.id ) stats runs once per customer. The aggregate has no GROUP BY, so it always returns exactly one row. For a customer with no orders, COUNT(*) returns 0 and SUM returns NULL, which is the missing total spend the prompt asks for. Because the lateral always returns one row, CROSS JOIN keeps every customer.

The trap

CROSS JOIN LATERAL drops outer rows whose lateral returns zero rows, which would lose customers with no orders. The aggregate without GROUP BY is what saves this query. COUNT(*) over an empty input is 0 and a one-row result, not zero rows, so the lateral always returns one row per customer. Swap in a non-aggregated lateral (SELECT id FROM orders WHERE o.customer_id = c.id) and the no-order customers vanish from the dashboard.

You practiced CROSS JOIN LATERAL (SELECT aggregate ...) where the inner aggregate has no GROUP BY — every outer record pairs with exactly one row from the lateral, even when zero child records match (COUNT returns 0, SUM returns missing).

Return every customer's ID, name, total number of orders placed, and total amount spent 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.