Lesson 11.5

SQL Common Table Expressions (CTEs)

A CTE (WITH) names an intermediate result so you can build a complex query as a sequence of clear steps.

A common table expression (CTE) names a query in a WITH block, and the statement underneath selects from that name as if it were a table. It lasts for the one statement only.

Pattern

with monthly as (
  select date_trunc('month', o.order_date) as m,
         sum(oi.quantity * oi.unit_price) as revenue
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
  group by m
)
select m, revenue
from monthly
order by m

Here monthly is the CTE. You are about to write one of these against Garden Shop, so the habit worth carrying in is the naming: call it for the result it produces. order_revenue reads better in the outer query than cte1, because the query then looks like it is selecting from a real table.

Syntax reference

Need syntax details? See the SQL CTE reference

Schema · Garden Shop Tables · order_items, orders, customers 3 tables

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Your task

Find the top 5 customers by lifetime revenue. Use a WITH block to total each customer's revenue, then join customers for their names and return first_name, last_name, and lifetime_revenue, biggest spender first.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: first_name, last_name, lifetime_revenue.
Rows: the top 5 customers by total spend.
The CTE computes revenue per customer; the outer query adds names and ranks them.

Previous · 11.4 Window functions

Next · 11.6 Filtering windows with QUALIFY →

Reference

SQL CASE Expressions

Searched and simple CASE patterns.

Cheatsheet

SQL CASE Expressions Cheatsheet

Searched and simple CASE, plus conditional aggregation.

Interview

SQL CTE Interview Questions

Practice WITH clauses and step-by-step reports.

Mistake