🟡 Subqueries

Subqueries: Think of a subquery as a chain of interrogation: before you can ask "which tests are slower than average?", someone must already have answered "what IS the average?".

Think of a subquery as a chain of interrogation: before you can ask "which tests are slower than average?", someone must already have answered "what IS the average?". The inner query produces that first answer; the outer query then makes a decision on top of it. So why can't we compress both steps into a single WHERE clause — why does `WHERE duration_ms > AVG(duration_ms)` fail? Because WHERE filters rows ONE AT A TIME, while the average needs the whole table to exist first; the aggregate wants the whole, WHERE only ever sees a part. A subquery exists precisely to fix that ordering problem. In Java you solve it with two separate statements: `double avg = list.stream().mapToInt(T::getDuration).average().orElse(0)`, then `list.stream().filter(t -> t.duration > avg)`. The real difference: Java gives you a **variable** holding the intermediate result; SQL does not — the inner query takes that variable's place and is re-evaluated on every run. For a QA engineer this is concrete: instead of exporting a 400-row nightly regression report into Excel to average it by hand, the pipeline answers "which tests drag the average up" in one query. A slow test is a flaky test candidate — anything creeping toward the timeout boundary starts failing at random in the next release.

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?

A subquery is a SELECT query nested inside another SQL statement. There are two primary types: - **Simple Subquery**: Runs independently of the outer query. It executes **once** first, computes its value, and hands it to the outer query. - **Correlated Subquery**: References columns from the outer query. It must execute **once for each row** evaluated by the outer query, which can create significant performance overhead on large tables. - **EXISTS Operator**: Checks for the existence of rows in a subquery. It stops searching as soon as it finds a single match, making it highly efficient.