Week 3 • Day 3Week 3

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(), or SUM() 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 BY collapses rows: If a model has 100 API calls, GROUP BY model_name collapses 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

FunctionWhat It ComputesExample 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!

sql
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:

powershell
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