🔴 CTEs

CTEs: Think of a CTE as the "prep" section of a recipe. A badly written recipe is one sentence: "pour over the pasta the sauce you obtained by adding the tomatoes you crushed sep

Think of a CTE as the "prep" section of a recipe. A badly written recipe is one sentence: "pour over the pasta the sauce you obtained by adding the tomatoes you crushed separately to the onions you diced and sautéed." A good one numbers the steps: 1) make the sauce, 2) boil the pasta, 3) combine. `WITH slow_tests AS (...)` is exactly that numbered step — it gives the intermediate result a **name**. But is this purely cosmetic? If a nested subquery produces the same result, why bother? Two real differences. First, reading direction: nested subqueries read **inside-out**, while human beings read top-down — three levels deep, not even you will know which parenthesis belongs to what six months later. Second, reuse: when two parts of the query need the same intermediate result, a subquery must be copy-pasted, whereas a CTE is simply called twice by name. Copy-pasted SQL is the kind of debt that silently produces a wrong report the day you update one copy and forget the other. The Java parallel is direct: breaking a long chain into `var slowTests = ...; var baseline = ...; return compare(slowTests, baseline);`. A CTE is SQL's local variable — except that, unlike Java's, it lives only as long as the single statement it belongs to. In QA reporting this is what lets a multi-step question like "which tests got slower than last week?" fit into one readable query: CTE1 holds this week's averages, CTE2 last week's, and the main query takes the difference. Written once and wired into the nightly pipeline, it catches performance regression before anyone notices it by hand.

CTEs — Common Table Expressions

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?