Name a Query with WITH
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 8 of 25.
A common table expression, or CTE, gives a query result a temporary name within one statement. Write WITH name AS (query) before the final SELECT. The named result behaves like a table for that statement.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
WITH paid AS (SELECT id, amount FROM orders WHERE status = 'paid') SELECT COUNT(*) AS paid_count FROM paid;The paid CTE contains four orders, so the outer query returns 4. It improves readability but does not create a persistent table. The next SQL statement cannot refer to paid.
WITH names an intermediate result for one statement; the final SELECT consumes it.
Challenge
MediumUse a CTE named stocked to select in-stock products. Return category and product_count for those products, ordered by category.
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.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
2Queries in Stages
Scalar SubqueriesCheck ExistenceName a Query with WITHChain CTEsAbove the Customer AveragePractice on your own: PostgreSQL playground