Most developers learn SQL through the standard curriculum: SELECT, WHERE, JOIN, GROUP BY, aggregate functions. This is enough to query data and get answers. It is not enough to write the queries efficiently that come up constantly in real applications.

Window functions and CTEs are the two SQL features that most change how you write queries once you know them. They’re not advanced or obscure - they’re part of standard SQL and available in every major database. They just don’t appear in most introductory courses.

CTEs: Readable Queries at Scale

A Common Table Expression (CTE) is a named subquery defined at the top of a query with WITH. It makes complex queries readable by letting you name intermediate results.

Without CTE:

SELECT u.name, order_counts.total
FROM users u
JOIN (
    SELECT user_id, COUNT(*) as total
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
) order_counts ON u.id = order_counts.user_id
WHERE order_counts.total > 5;

With CTE:

WITH recent_orders AS (
    SELECT user_id, COUNT(*) as total
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.name, ro.total
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
WHERE ro.total > 5;

The query does the same thing. The CTE version is readable because recent_orders tells you what the subquery represents. On complex queries with multiple subqueries, this is significant.

Multiple CTEs - you can chain them, each building on the previous:

WITH 
active_users AS (
    SELECT id FROM users WHERE last_login > NOW() - INTERVAL '90 days'
),
user_revenue AS (
    SELECT o.user_id, SUM(o.amount) as total_revenue
    FROM orders o
    JOIN active_users au ON o.user_id = au.id
    GROUP BY o.user_id
),
top_customers AS (
    SELECT user_id
    FROM user_revenue
    WHERE total_revenue > 1000
)
SELECT u.name, ur.total_revenue
FROM users u
JOIN user_revenue ur ON u.id = ur.user_id
JOIN top_customers tc ON u.id = tc.user_id
ORDER BY ur.total_revenue DESC;

Three named steps, each clear in its intent, building toward the final result.

Recursive CTEs - this is where CTEs go beyond readability into capability. Recursive CTEs can query hierarchical data structures: org charts, category trees, threaded comments, graph traversals.

-- Find all employees in a management chain
WITH RECURSIVE subordinates AS (
    -- Base case: the manager themselves
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 42  -- start from employee #42

    UNION ALL

    -- Recursive case: direct reports of each found employee
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Without recursive CTEs, this kind of query requires either multiple round trips to the database from application code, or a stored procedure. The recursive CTE does it in one query.

Window Functions: Aggregation Without Collapsing Rows

The key limitation of GROUP BY is that it collapses rows. If you GROUP BY user_id to count orders per user, you lose the individual order rows - you can only see aggregated results.

Window functions apply an aggregate function over a set of rows while keeping the individual rows visible. The “window” is the set of rows to aggregate, defined per row.

SELECT 
    user_id,
    order_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id) as user_total
FROM orders;

Result:

user_id | order_id | amount | user_total
--------|----------|--------|----------
1       | 101      | 50.00  | 150.00
1       | 102      | 100.00 | 150.00
2       | 103      | 75.00  | 75.00

Every row keeps its individual data. The window function adds user_total as a per-row annotation: the sum of all amounts for that user. No rows are collapsed.

Ranking

The most common window function use: ranking rows within a partition.

SELECT 
    user_id,
    product_id,
    purchase_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date DESC) as purchase_rank
FROM purchases;

This assigns rank 1 to each user’s most recent purchase, rank 2 to the second most recent, and so on. Now you can filter: WHERE purchase_rank = 1 gives you each user’s most recent purchase - one row per user.

Three ranking functions with different tie behavior:

• ROW_NUMBER() - always unique, arbitrary tiebreak
• RANK() - tied rows get the same rank, next rank skips (1, 1, 3)
• DENSE_RANK() - tied rows get the same rank, no skip (1, 1, 2)

Running Totals and Moving Averages

SELECT 
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) as running_total,
    AVG(daily_revenue) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_avg
FROM daily_sales;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines the window frame: the current row and the 6 preceding rows. The moving average slides forward as the ordering progresses. This would be painful to write with a self-join or a subquery; with window functions it’s one pass through the data.

LAG and LEAD: Comparing to Adjacent Rows

SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) as month_over_month_change
FROM monthly_revenue;

LAG(revenue, 1) gives you the value of revenue from the previous row (offset 1). LEAD(revenue, 1) gives you the next row. Useful for calculating period-over-period changes without a self-join.

A Practical Example: Top N Per Group

One of the most common queries in applications: “give me the top 3 products by sales in each category.” Classic SQL makes this awkward. Window functions make it clean:

WITH ranked_products AS (
    SELECT 
        p.category_id,
        p.name,
        SUM(oi.quantity * oi.unit_price) as revenue,
        RANK() OVER (
            PARTITION BY p.category_id 
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        ) as rank_in_category
    FROM products p
    JOIN order_items oi ON p.id = oi.product_id
    GROUP BY p.category_id, p.id, p.name
)
SELECT category_id, name, revenue
FROM ranked_products
WHERE rank_in_category <= 3
ORDER BY category_id, rank_in_category;

CTE to name the intermediate result. Window function to rank within category. Filter on the rank. One query, one pass through the data.

Performance

CTEs in most databases (PostgreSQL, SQL Server, recent MySQL versions) are optimization fences by default - the query planner treats them as black boxes and may or may not optimize across them. For large datasets, sometimes a subquery inline lets the planner make better decisions.

Window functions are generally efficient. They typically require a single sort pass over the data. The frame definition (ROWS BETWEEN ...) can affect performance - unbounded frames are cheaper than bounded ones because the database can stream without looking ahead.

Both features are worth understanding not just for correctness but for the questions they let you ask in a single query rather than in application code. Every round trip to the database is expensive. Every piece of data filtering you move from the application to the database reduces what you have to transfer and process.