Advanced SQL: Window Functions and CTEs

Once you know the basics, two features transform what SQL can do: common table expressions and window functions.

Common Table Expressions (CTEs)

A CTE names an intermediate result, making complex queries readable:

WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT month, revenue
FROM monthly
ORDER BY month;

Window Functions

Window functions calculate across related rows without collapsing them into groups.

Running total:

SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly;

Ranking within groups:

SELECT region, product, revenue,
       RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region
FROM product_sales;

Comparing with the previous period:

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

Moving average:

AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Useful Functions

ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, NTILE, and aggregates used with OVER.

Common Uses

Top-N per group, deduplication (keep the latest row per ID with ROW_NUMBER), cohort analysis, retention and period-over-period comparisons.

Tip

Syntax varies slightly between databases — check your platform's documentation for date functions and window-frame details.