Menu
CoddyTech

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:

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 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 icon

Challenge

Medium

Use 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.
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