Day 3:Common Table Expressions
Battle-tested daily lesson note • 100% runnable code, interview Q&A, test suite & interactive quiz
Week 3 — Day 3: Common Table Expressions (WITH CTEs) & Window Functions (OVER)
Mentor's Note (10+ YOE Staff AI Engineer POV): Ask any Data Scientist, Backend Engineer, or AI Engineer who interviewed at Google, Meta, Amazon, or OpenAI: "What was the hardest SQL question in your interview?" 95% of the time, the answer is: A Window Function (
ROW_NUMBER(),RANK(),DENSE_RANK(),LAG(), orSUM() OVER) combined with a Common Table Expression (WITH)!Today we demystify Window Functions completely so you can write them in your sleep!
Part 1: Why GROUP BY Isn't Enough — And How Window Functions (OVER) Work
GROUP BYcollapses rows: If a model has 100 API calls,GROUP BY model_namecollapses all 100 rows into 1 single summary row. You lose the individual call details!- Window Functions (
OVER (PARTITION BY ... ORDER BY ...)) DO NOT collapse rows! They compute a ranking, running total, or previous-row comparison alongside every individual row!
The 5 Must-Know Window Functions
| Function | What It Computes | Example Use Case in AI Engineering |
|---|---|---|
ROW_NUMBER() OVER (...) | Unique sequential number (1, 2, 3, 4) within each partition (no ties!). | Fetching the Top 2 slowest LLM queries per model (WHERE rn <= 2) or deduplicating rows! |
RANK() OVER (...) | Rank with gaps on ties (1, 2, 2, 4). | Ranking models on a benchmark leaderboard. |
DENSE_RANK() OVER (...) | Rank without gaps on ties (1, 2, 2, 3). | Finding the 2nd highest distinct evaluation score. |
LAG(col, 1) OVER (...) | Looks backward 1 row in the ordered partition (LEAD looks forward 1 row!). | Comparing Prompt v2's accuracy score directly against Prompt v1's previous score (score - LAG(score)). |
SUM(col) OVER (PARTITION BY ... ORDER BY ...) | Cumulative Running Total row by row! | Tracking a user's cumulative token spend across a conversation to trigger quota cutoffs! |
Part 2: Common Table Expressions (WITH cte_name AS (...))
Instead of writing unreadable nested subqueries (SELECT * FROM (SELECT * FROM (...))), use a CTE (WITH) at the top of your query to name each logical step cleanly!
WITH ranked_queries AS (
SELECT
id,
model_name,
latency_ms,
ROW_NUMBER() OVER (
PARTITION BY model_name
ORDER BY latency_ms DESC
) AS latency_rank
FROM llm_traces
)
SELECT id, model_name, latency_ms, latency_rank
FROM ranked_queries
WHERE latency_rank <= 2; -- Top 2 slowest queries per model!
Part 3: Hands-On Lab — CTEs & Window Function Telemetry Analyzer (sql_window_functions.py)
Check out src/sql_window_functions.py (in Tab 2: Codes — click Run in Browser to execute) and run it now:
python week03_sql_databases_from_zero_to_pro/day03_subqueries_ctes_and_window_functions/src/sql_window_functions.py
python -m unittest discover -s week03_sql_databases_from_zero_to_pro/day03_subqueries_ctes_and_window_functions/tests -v