.png)
3 months ago
2
“Common Table Expressions turn spaghetti SQL into lego blocks, while Window Functions give you super‑powers to look across rows.”
If you’ve ever stared at a monstrous SQL query and wished for modules and variables, or found yourself writing nested sub‑queries just to compute a running total — CTEs and Window Functions are here to make your life easier and your queries faster.
In this guide you’ll learn:
- What CTEs are and why the WITH clause is your friend
- How Window Functions let you peek across rows without GROUP BY gymnastics
- Performance considerations & best practices
- Real‑world patterns: cohort retention, top‑N per category, year‑over‑year deltas
- Handy references and further reading
A Common Table Expression (CTE) is a named, temporary result set that exists only for the duration of the query. Declared with WITH, a CTE acts like an inline view—but far more readable.
WITH weekly_births AS (SELECT date_trunc('week', day) AS week,
SUM(births) AS births
FROM births
GROUP BY 1
)
SELECT week,
births,
LAG(births, 1) OVER (ORDER BY week) AS prev_week
FROM weekly_births;
