Subqueries
A query can feed another query
A subquery returns a value, a list or a table to the query around it. EXISTS asks only whether any row matches.
To find orders above average, you first need the average, and that is itself a query. The inner query runs and returns one number, 4.11. The outer query uses it in WHERE, and 2 orders pass. A subquery can return one value, a list for IN, a whole table to select from, or just the answer to whether any row exists.
Common table expressions
Name each step, and the query reads top to bottom
A CTE, written with WITH, names an intermediate result so the next step can use it.
Real analysis takes several steps, and nesting them as subqueries soon becomes unreadable. A WITH clause names each step instead. Monthly: total sales per month and region. Ranked: within each month, rank the regions by total. And finally, keep the regions ranked first. Each step reads from the one above it, so the query reads like a recipe, top to bottom.
Common table expressions · Going deeper
Recursive CTEs: walking a hierarchy
A recursive CTE joins an anchor row to the table again and again until no new rows appear.
Window functions
Compute over a group of rows without collapsing them
A window function uses OVER (PARTITION BY …) to compute across related rows while keeping every row.
Grouping answers how much did each customer spend, but throws the orders away. 6 rows go in, 3 come out. A window function does the same sum, but writes it onto every row. OVER, partition by customer, means: for this row, look at all the rows with the same customer. Now each order sits beside its customer's total, and you can work out its share.
Running totals
A frame slides down the rows, one row at a time
With ORDER BY, a window has a direction; the frame picks which rows around the current one count. On day 5: running total 640, 3-day average 146.7.
Add ORDER BY inside the window and it becomes a moving thing. For a running total, the frame runs from the first row to the current one. On day 5 it covers days one to 5: 640. For a three-day moving average, the frame is the current row and the two before it. The moving average smooths the daily noise into a trend.
Running totals · Going deeper · 1 of 2
LAG and LEAD: compare with the row before
LAG and LEAD read a value from an earlier or later row in the window, so change over time is one column.
ROWS, RANGE and GROUPS · Going deeper · 2 of 2
ROWS counts rows, RANGE counts values, and ties show the difference
With no frame written, a window is RANGE to the current row, so tied rows share a result. ROWS counts each row on its own.
Ranking
Three ranking functions differ only in how they treat ties
ROW_NUMBER never ties; RANK ties then skips (1, 2, 2, 4, 4, 6); DENSE_RANK ties without gaps (1, 2, 2, 3, 3, 4).
Ranking looks simple until two rows tie. ROW_NUMBER numbers rows one to six no matter what, so Ben and Chen get two and three in no guaranteed order. RANK gives tied rows the same rank, then skips: two, two, four. DENSE RANK gives the same rank without skipping: two, two, three. The most common use is top N per group: number the rows within each team and keep those numbered one or two.
Views
A view stores the question; a materialised view stores the answer
A view runs its query on every read. A materialised view stores the result and is fast, but stale until refreshed.
Both save a query under a name. The difference is when the work happens. A plain view runs its query every time someone reads it. Always current, and exactly as slow as the query. A materialised view runs the query once, stores the result, and serves reads from that. Reads are fast, but the result is only as fresh as the last refresh.
Views in practice · Going deeper
Views can hide logic, accept writes, or stay fresh on their own
A secure view hides its definition; an updatable view accepts writes; each platform keeps a materialised view fresh its own way.
A secure view hides its definition; an updatable view accepts writes; each platform keeps a materialised view fresh its own way. Plain views. Updatable views a view over one table, with no GROUP BY, DISTINCT or aggregates, accepts INSERT, UPDATE and DELETE. Add WITH CHECK OPTION and a row the view could not show is refused: PostgreSQL answers “new row violates check option for view”. Secure views (Snowflake) CREATE SECURE VIEW hides the definition from other roles and stops the optimiser pushing a user’s filter below the view’s own, which could leak hidden rows. They can be slower, and they are what a data share normally exposes. Dynamic views (Databricks) a view that calls is_account_group_member() to mask columns or filter rows by the reader’s group. Materialised views, by platform. Snowflake dynamic tables go one step further: you declare the query and how stale the result may be (TARGET_LAG = '5 minutes'), and Snowflake schedules the refreshes, incrementally where it can. Materialised views in a Databricks pipeline work the same way.
Plain views
- Updatable views a view over one table, with no GROUP BY, DISTINCT or aggregates, accepts INSERT, UPDATE and DELETE. Add WITH CHECK OPTION and a row the view could not show is refused: PostgreSQL answers “new row violates check option for view”.
- Secure views (Snowflake) CREATE SECURE VIEW hides the definition from other roles and stops the optimiser pushing a user’s filter below the view’s own, which could leak hidden rows. They can be slower, and they are what a data share normally exposes.
- Dynamic views (Databricks) a view that calls is_account_group_member() to mask columns or filter rows by the reader’s group.
Materialised views, by platform
MERGE
Update what matches, insert what is new, in one statement
MERGE, also called an upsert, applies a batch of changes: update matches, insert new rows, delete when told.
Loading changes into a table is the daily work of a data engineer, and MERGE does it in one statement. The target table holds three customers. A batch of changes arrives: Ben moved, Dev is new, Chen asked to be deleted. MERGE matches them on id. Ben matches, so his row is updated. Dev does not, so he is inserted. Chen matches with a delete flag, so she is removed. Run it again with the same batch and nothing changes, which is what makes a retry safe.
Upsert syntax · Going deeper
Every engine spells upsert differently, and all refuse duplicates
PostgreSQL also has INSERT … ON CONFLICT; Delta adds WHEN NOT MATCHED BY SOURCE. Two source rows for one key make MERGE fail.
Lesson complete
You finished “SQL for analysis”
Nicely done.