Chained CTEs in SQL

Several CTEs, comma separated, each free to build on the one before.

WITH monthly AS (
    SELECT
        STRFTIME('%Y-%m', placed_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY month
),
ranked AS (
    SELECT
        month,
        revenue,
        RANK() OVER (
            ORDER BY revenue DESC
        ) AS position
    FROM monthly
)
SELECT *
FROM ranked
WHERE position <= 3;

How it works

1. This is how a long report stays legible: one step per CTE.
2. A later CTE selects from an earlier one by name.
3. The final SELECT joins the pieces together.

Keywords and builtins used here

• AS
• BY
• DESC
• FROM
• GROUP
• ORDER
• SELECT
• SUM
• WHERE
• WITH
• month
• position

The run, in numbers

Lines

20

Characters to type

279

Tokens

64

Three-star pace

90 tpm

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

Type this snippet

Step 2 of 3 in Common table expressions, step 5 of 24 in Analytics & reporting.

← Previous Next →