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;

🔐

This part is for subscribers

A subscription unlocks every premium series and its Jupyter notebooks.

You need a free account to subscribe. Cancel anytime.

Mastering CTE: Escape Subquery Hell Once and For All

TL;DR

This part is for subscribers

Files for this post