🟡 Aggregate Functions

Aggregate Functions: Aggregate functions work like an accountant who scans hundreds of rows and hands you a single summary value: instead of reading every row yourself to answer

Aggregate functions work like an accountant who scans hundreds of rows and hands you a single summary value: instead of reading every row yourself to answer 'what was the longest test duration this month?' you write MAX(duration_ms) and get the answer instantly. Doesn't Java's `list.stream().mapToInt(t -> t.durationMs).max()` chain do the same? It does — but it first loads all records into memory; the database computes MAX via an index, often finishing with a single disk read. The core difference between Java's Collectors.summarizingInt() and SQL aggregates is: without GROUP BY, SQL treats the entire table as one group; add GROUP BY and each group gets its own aggregation calculation. For a QA engineer the critical use of aggregates is monitoring test run statistics: `AVG(duration_ms) GROUP BY environment` reveals which environment is slowing tests down, and `COUNT(*) WHERE status='FAIL' GROUP BY test_name` clearly shows which tests are flaky — without this data it is impossible to prioritize which tests to fix first.

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?

Aggregate Functions and the GROUP BY Rule