🔴 Window Functions

Window Functions: Think of GROUP BY as burning the class photo and pinning up a single sheet that says "class average: 62" — you keep the summary and lose the students.

Think of GROUP BY as burning the class photo and pinning up a single sheet that says "class average: 62" — you keep the summary and lose the students. A window function leaves the photo intact and clips a small label onto each student instead: "class average 62, you scored 78, you rank 3rd". The rows survive; context is added. So why isn't GROUP BY enough — why learn a new concept when you could compute the average once and stitch the results together yourself? Because the question "where does each test stand relative to its own environment's average?" requires the row and the summary to sit side by side in the SAME result. With GROUP BY you'd have to join the table back onto itself; the query doubles in size, and the moment you get the join condition wrong the rows multiply silently. In Java the equivalent is two passes: build a Map with `Collectors.groupingBy(..., averagingInt(...))`, then walk the list again comparing each element against that Map. `OVER (PARTITION BY environment)` collapses both passes into one expression — no intermediate Map, no second loop. For QA this is the core flaky-hunting tool: `ROW_NUMBER() OVER (PARTITION BY test_name ORDER BY run_date DESC)` isolates each test's LATEST run, and `AVG(duration_ms) OVER (PARTITION BY environment)` surfaces tests that behave normally on staging but run at twice the average in prod. That second one is usually the first hard evidence that the environment is broken, not the test.

Window functions perform calculations across a "window" of related rows WITHOUT collapsing them like GROUP BY does. Each row gets its own result while also knowing about surrounding rows.

Micro Lab: SQL — GROUP BY / CTE / Window

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

Step by Step: SQL — GROUP BY / CTE / Window

Aggregation or Window?

Determine: aggregation (summarizing) or Window (row-by-row computing)?

For aggregation, select the summary column with GROUP BY

Apply condition on groups with HAVING — like WHERE but after aggregation

Make modular with CTE

Modularize query with WITH cte_name AS (...) SELECT ... FROM cte_name

Add OVER(PARTITION BY)

For Window, use SUM(col) OVER (PARTITION BY partition ORDER BY sort)

What is the SQL GROUP BY and Window Function query writing order?