Data & Analytics•SOTAAZ Lab•December 31, 2025•KR

Mastering CTE: Escape Subquery Hell Once and For All

One WITH clause transforms unreadable queries into clear, logical steps. Recursive CTEs handle hierarchies with ease.

Mastering CTE: Escape Subquery Hell Once and For All

One WITH clause transforms unreadable queries into clear, logical steps. Recursive CTEs handle hierarchies with ease.

TL;DR

CTE (Common Table Expression): Temporary named result set within a query
Readability: Break complex subqueries into named, logical steps

Reusability: Reference the same CTE multiple times
Recursive CTE: Handle hierarchies, generate date series, and more

1. What is a CTE?

The Subquery Problem

sql

-- Hard to read nested subqueries
SELECT
    user_id,
    total_amount,
    user_avg,
    total_amount - user_avg as diff
FROM (
    SELECT
        user_id,
        SUM(amount) as total_amount,
        AVG(SUM(amount)) OVER () as user_avg
    FROM (
        SELECT
            user_id,
            amount
        FROM orders
        WHERE status = 'completed'
          AND created_at >= '2024-01-01'
    ) filtered_orders
    GROUP BY user_id
) user_totals
WHERE total_amount > user_avg;

The rest of this series is paid

✓ The whole “SQL analytics” series: 9 posts in English, 9 in Korean
✓ 7 Jupyter notebooks
✓ One payment, no expiry. Opens when you sign in with the email you pay with.

The series pass needs no account to pay. The subscription needs a free account.

Mastering CTE: Escape Subquery Hell Once and For All

TL;DR

The rest of this series is paid

Files for this post