High Value Customer Order Stats — LATERAL Joins in SQL

The problem

Brightlane's VIP account team is analyzing purchase patterns for high-spending customers — those who have placed at least one order exceeding $500.

Write a query to return every high-spending customer's ID, name, total order count, and highest single-order amount on their account.

Assumptions:

A high-spending customer is one who has at least one order with total_amount strictly greater than $500.
For each qualifying customer, the order count is the total number of orders ever placed by that customer (not just the high-value ones), and the highest single-order amount is the largest total_amount across all their orders.
Only high-spending customers should appear in the result.

Output:

One row per high-spending customer, with columns id, name, order_count, and max_amount.

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

WITH
  high_value_customers AS (
    SELECT
      id,
      name
    FROM
      customers
    WHERE
      id IN (
        SELECT
          customer_id
        FROM
          orders
        WHERE
          total_amount > 500
      )
  )
SELECT
  hvc.id,
  hvc.name,
  order_stats.order_count,
  order_stats.max_amount
FROM
  high_value_customers hvc
  CROSS JOIN LATERAL (
    SELECT
      COUNT(*) AS order_count,
      MAX(total_amount) AS max_amount
    FROM
      orders o
    WHERE
      o.customer_id = hvc.id
  ) order_stats

The shape

A CTE stages the high-spending customers, and the lateral then computes per-customer stats over the full order history for those staged customers. Restricting the outer set first means the lateral runs only for customers who qualify; computing the aggregates inside the lateral means each qualifying customer's stats cover all their orders, not just the high-value ones. That two-layer split is exactly what the VIP team asked for.

Clause by clause

WITH high_value_customers AS ( SELECT id, name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total_amount > 500) ) stages the qualifying customers. The subquery in IN returns the customer ids attached to any order over $500, and the outer WHERE keeps only those customers. The CTE materialises the restricted set once.
SELECT hvc.id, hvc.name, order_stats.order_count, order_stats.max_amount returns the four columns the prompt asks for.
FROM high_value_customers hvc is the driving table for the lateral. Only qualifying customers are in scope from here on.
CROSS JOIN LATERAL ( SELECT COUNT(*) AS order_count, MAX(total_amount) AS max_amount FROM orders o WHERE o.customer_id = hvc.id ) order_stats runs once per qualifying customer. The correlated filter o.customer_id = hvc.id scopes the aggregate to that customer's full order history, not just their high-value orders. COUNT(*) and MAX(total_amount) reduce to one row, and CROSS JOIN pairs it with the outer customer.

Why this and not a single-stage join

FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.id IN (SELECT customer_id FROM orders WHERE total_amount > 500) GROUP BY c.id, c.name reaches the same numbers on this data. Two reasons to prefer the staged form. First, the qualification logic is named (high_value_customers) instead of buried in a WHERE ... IN, which is easier for the VIP team to read and to modify (raise the threshold to $1000, add a recency clause). Second, the lateral form computes the aggregates once per qualifying customer with the customer set already restricted, instead of grouping over the full join and then filtering.

The trap

The qualification filter (> 500) belongs in the CTE, not in the lateral. Move the total_amount > 500 predicate into the lateral's WHERE and the aggregates collapse to "count of high-value orders, max of high-value amounts," which is not what the prompt asks for. The prompt's split is deliberate: use the high-value orders to qualify the customer, then summarise the customer's complete history. The CTE selects who is in the result; the lateral describes everything about them.

You practiced staging a restricted record set in a WITH clause and feeding it into a CROSS JOIN LATERAL — the lateral computes per-record statistics over the full child set even though the outer set was already restricted to qualifying records.

Return every high-spending customer's ID, name, total order count, and highest single-order amount on their account

The shape

Clause by clause

Why this and not a single-stage join

The trap

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