Menu
CoddyTech

Chain CTEs

Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 9 of 25.

A WITH clause can contain several named queries separated by commas. A later CTE may read an earlier one. Use separate stages when the second calculation depends on the grain of the first.

Input table orders:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"channel":"web","items":2}
WITH totals AS (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id), large AS (SELECT customer_id, total FROM totals WHERE total >= 50) SELECT customer_id, total FROM large ORDER BY customer_id;

The first stage produces one row per ordering customer. The second retains Ari and Cleo at totals of 50. Naming the stages keeps the grouping rule separate from the threshold.

Separate dependent stages with comma-separated CTEs and finish with one SELECT.

challenge icon

Challenge

Medium

Build per-customer totals for paid orders, then keep customers with paid totals above 25. Return customer_id and paid_total ordered by customer_id, using two CTEs.

The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.

Try it yourself

-- Write your SQL here.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground