lesson 49 Advanced product analysis · advanced

Capstone: customer insights

Bring filtering, joins and aggregation together for customer analysis.

the idea

Combine filtering, joins, aggregation and ranking to answer a business question. First compute each customer's paid revenue, then rank those customer totals. Write one stage at a time.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

customers4 columns · 8 rows

idinteger · key	nametext	citytext	joined_attext
1	Alice	Chicago	2024-01-10
2	Ben	Austin	2024-02-12
3	Cara	Seattle	2024-03-05
4	Dev	Chicago	2024-04-20
5	Elena	NULL	2024-05-01

showing 5 of 8 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

WITH revenue AS (
  SELECT c.name,SUM(o.quantity*p.price) AS paid_revenue
  FROM customers c
  JOIN orders o ON o.customer_id=c.id
  JOIN products p ON p.id=o.product_id
  WHERE o.status='paid'
  GROUP BY c.id,c.name
)
SELECT
  name,
  paid_revenue,
  DENSE_RANK() OVER(ORDER BY paid_revenue DESC) AS revenue_rank
FROM revenue;

Watch for: Rank customer totals, not individual order amounts.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

Assemble paid revenue pieces. Customer insights join customers, paid orders, and product prices before summing. Check the join keys first.

step-1.sql

SELECT c.name, o.quantity, p.price, o.status
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN products p ON p.id = o.product_id
WHERE o.status = 'paid'
LIMIT 8;

Summarize paid revenue per customer. The capstone CTE pattern computes paid_revenue, then the outer query ranks or filters those totals.

step-2.sql

WITH revenue AS (
  SELECT c.name, SUM(o.quantity * p.price) AS paid_revenue
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  JOIN products p ON p.id = o.product_id
  WHERE o.status = 'paid'
  GROUP BY c.name
)
SELECT name, paid_revenue
FROM revenue
ORDER BY paid_revenue DESC;