Common table expression in SQL

WITH names a result set up front so the main query reads like prose.

WITH paid_orders AS (
    SELECT
        customer_id,
        total
    FROM orders
    WHERE status = 'paid'
)
SELECT
    customer_id,
    COUNT(*) AS orders,
    SUM(total) AS revenue
FROM paid_orders
GROUP BY customer_id;

How it works

1. The CTE is written first and referenced by name below.
2. It replaces a nested subquery with something you can read.
3. One CTE can be referenced more than once.

Keywords and builtins used here

• AS
• BY
• COUNT
• FROM
• GROUP
• SELECT
• SUM
• WHERE
• WITH

The run, in numbers

Lines

13

Characters to type

184

Tokens

37

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 25 seconds.

Type this snippet

Step 1 of 3 in Common table expressions, step 4 of 24 in Analytics & reporting.

← Previous Next →