Subqueries

A query can feed another query

SELECT order_id, total FROM ordersWHERE total > (SELECT AVG(total) FROM orders);4.11inner query runs firstorder_idcustomertotal101Ben1.20102Asha6.50103Ben3.00104Chen8.75105Ben1.20106Asha4.002 orders are above 4.11Three places a subquery can sitWHERE x > (SELECT …)one valueWHERE id IN (SELECT …)a listFROM (SELECT …) AS ta whole tableWHERE EXISTS (SELECT …)is there any row?A query can feed another query: the inner result becomes a value, a list or a table for the outer one.This subquery runs once. A correlated subquery refers to the outer row, such as the average of this customer’s own orders, and logicallyruns once per row; optimisers often rewrite it as a join, but not always. EXISTS stops at the first match, which makes it the safe, fastway to ask “has this customer ever ordered?”

A subquery returns a value, a list or a table to the query around it. EXISTS asks only whether any row matches.

Common table expressions

Name each step, and the query reads top to bottom

WITH monthly AS ( SELECT month, region, SUM(amount) AS total FROM sales GROUP BY month, region), ranked AS ( SELECT *, RANK() OVER (PARTITION BY month ORDER BY total DESC) AS r FROM monthly)SELECT month, region, totalFROM ranked WHERE r = 1;monthlymonthregiontotal2025-01North4102025-01South3802025-02North4502025-02South5202025-03North3002025-03South470rankedr122121result: best region each monthmonthregiontotal2025-01North4102025-02South5202025-03South470A WITH clause names each step, so a long query reads top to bottom like a recipe.Each common table expression (CTE) is a named result the next step can use. It replaces nested subqueries that must be read inside out, andone CTE can be referred to several times. dbt models (lesson 10) are the same idea spread across files.

A CTE, written with WITH, names an intermediate result so the next step can use it.

Common table expressions · Going deeper

Recursive CTEs: walking a hierarchy

WITH RECURSIVE chain AS ( SELECT name, 0 AS level FROM staff WHERE manager IS NULL -- anchor UNION ALL SELECT s.name, c.level + 1 FROM staff s JOIN chain c ON s.manager = c.name -- step)SELECT * FROM chain;Ana · level 0Bo · level 1Di · level 2Ed · level 3Cy · level 1A recursive CTE starts from an anchor and repeats a step until no new rows appear: the way to walk a hierarchy.Each round joins the rows found so far to the table again. Org charts, bills of materials, folder trees and graph paths all need it.Guard against cycles (a manager loop never ends): PostgreSQL has a CYCLE clause, others need a depth limit in the WHERE.

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

GROUP BY collapsesSELECT customer, SUM(total)FROM orders GROUP BY customercustomersumAsha10.50Ben5.40Chen8.756 rows in, 3 rows out: the orders are goneA window keeps every rowSELECT order_id, customer, total, SUM(total) OVER (PARTITION BY customer) AS cust_sumorder_idcustomertotalcust_sumshare102Asha6.5010.5062%106Asha4.0010.5038%101Ben1.205.4022%103Ben3.005.4056%105Ben1.205.4022%104Chen8.758.75100%A window function computes over a group of rows but returns a value on every row, so detail and total sit side by side.OVER (PARTITION BY customer) defines the window: the rows that share this row’s customer. Nothing is collapsed, so each order can becompared with its customer’s total, here as a share. Any aggregate (SUM, AVG, COUNT, MIN, MAX) can be used as a window function.

A window function uses OVER (PARTITION BY …) to compute across related rows while keeping every row.

Running totals

A frame slides down the rows, one row at a time

SUM(sales) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,AVG(sales) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3_daysdaysalesrunning_totalavg_3_days1120120120.0280200100.03150350116.7490440106.75200640146.7660700116.77140840133.3running3-row framedaily sales; the line is the 3-day averageOn day 5 the running total frame covers days 1 to 5 (640); the moving-average frame covers days 3 to 5 (146.7).ORDER BY inside OVER gives the window a direction; the frame says which rows around the current one to use. With ORDER BY and noframe, the default is RANGE UNBOUNDED PRECEDING to CURRENT ROW: a running total, but rows that tie on the ORDER BY value moveas one step. Write ROWS explicitly when each row should count on its own.

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.

Running totals · Going deeper · 1 of 2

LAG and LEAD: compare with the row before

SELECT day, sales, LAG(sales) OVER (ORDER BY day) AS prev, sales - LAG(sales) OVER (ORDER BY day) AS changedaysalesprevchange1120NULLNULL280120-40315080+70490150-60520090+110660200-140714060+80LAG(x)the value from the row beforeLEAD(x)the value from the row afterFIRST_VALUE(x)the first value in the frameDay 1 has no previous row, so prev is NULL.LAG and LEAD reach into the previous or next row, so change over time becomes one column.Day-over-day change, the gap between a user’s sessions, the time between an order and its delivery: all are LAG or LEAD within apartition. LAG(sales, 7) reaches back seven rows, for week-on-week change.

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

SUM(sales) OVER (ORDER BY day <frame>)↓ what you get when no frame is writtendaysalesROWS unboundedRANGE unboundedRANGE 1 PRECEDINGGROUPS 1 PRECEDING112012012012012028020035035035021503503503503503904404403203205200640640200290ROWSphysical rows: the two day-2rows get 200, then 350RANGEvalues: rows that tie on dayare peers and share 350GROUPSpeer groups: “1 preceding”is the previous day presentDay 5: RANGE 1 PRECEDING looks for day 4, finds no rows, returns 200; GROUPS 1 PRECEDING takes the previous day that exists, day 3: 290.LAST_VALUE(sales) OVER (ORDER BY day) returns the current row’s last peer, not the final row: the default frame stops at the current row.With ORDER BY and no frame, the window is RANGE up to the current row, so tied rows move together.That default is why a running total over a date with several rows per day jumps once per day, not once per row. Write ROWSBETWEEN … when each row should count, or add a tie-breaker to ORDER BY. RANGE with an offset needs one numeric or date ORDERBY column. GROUPS is newer (PostgreSQL 11, SQLite, DuckDB) and missing from several engines, SQL Server among them. A namedWINDOW w AS (…) clause saves repeating a frame.

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

playerscoreROW_NUMBERRANKDENSE_RANKAsha92111Ben88222Chen88322Dev75443Eli75543Fay70664ROW_NUMBERalways 1, 2, 3 …; ties broken arbitrarilyRANKties share a rank, then a gap: 1, 2, 2, 4, 4, 6DENSE_RANKties share, no gap: 1, 2, 2, 3, 3, 4Top 2 per group:QUALIFY ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC) <= 2QUALIFY: Snowflake, Databricks, BigQuery; else a subqueryThree ranking functions differ only in what they do with ties.Use ROW_NUMBER to keep exactly one row per group, such as the latest version of each record when removing duplicates (lesson 10). Use RANKor DENSE_RANK when tied rows must be treated the same. NTILE(4) splits rows into quartiles; PERCENT_RANK gives a 0 to 1 position.

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).

Views

A view stores the question; a materialised view stores the answer

Viewa saved query; it runs on every readread 14.1 sread 24.1 sread 34.1 salways current, and as slow as its query, every timeMaterialised viewa saved result: refreshed once, then readrefresh4.1 s, onceread 10.02 sread 20.02 sread 30.02 sfast to read; stale until the next refreshA view stores the question; a materialised view stores the answer, and must be refreshed to stay true.Views simplify and secure access (lesson 2) but do no work ahead of time. A materialised view trades freshness and storage for speed.PostgreSQL refreshes on command (REFRESH MATERIALIZED VIEW); Snowflake and BigQuery maintain them automatically; Databricks keepsthem current in pipelines. Timings are illustrative; bars are drawn to scale.

A view runs its query on every read. A materialised view stores the result and is fast, but stale until refreshed.

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.

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

PostgreSQLREFRESH MATERIALIZED VIEW by hand or on a schedule. CONCURRENTLY keeps it readable during the refresh, and needs a unique index on the view.
Snowflakemaintained automatically in the background, at a credit cost. Enterprise Edition. One table only: no joins, no window functions.
Databricksdefined in SQL or a pipeline; refreshed on a schedule or trigger, incrementally where the query allows.
RedshiftAUTO REFRESH YES, and the optimiser can rewrite a query to use the view without being asked.
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.

MERGE

Update what matches, insert what is new, in one statement

MERGE INTO customers t USING changes s ON t.id = s.idWHEN MATCHED AND s.op = 'D' THEN DELETEWHEN MATCHED THEN UPDATE SET city = s.cityWHEN NOT MATCHED THEN INSERT (id, name, city) VALUES (s.id, s.name, s.city);customers (before)idnamecity1AshaPune2BenLeeds3ChenPerthchangesidnamecityop2BenYorkU4DevLeedsI3ChenNULLDcustomers (after)idnamecity1AshaPune2BenYorkupdated4DevLeedsinsertedChen deletedMERGE applies a batch of changes in one statement: update the rows that match, insert the ones that do not, delete if told to.It is the workhorse of incremental loading (lesson 10) and of applying change data capture (lesson 11). Run it twice with the same input andthe table ends the same, which makes a retried load safe. Delta Lake, Snowflake, SQL Server and PostgreSQL 15+ all support MERGE.

MERGE, also called an upsert, applies a batch of changes: update matches, insert new rows, delete when told.

Upsert syntax · Going deeper

Every engine spells upsert differently, and all refuse duplicates

EngineUpsert as writtenNoteSQL standard: SQL Server, Oracle,MERGE INTO t USING s ON t.id = s.idthe statement on the main pageSnowflake, Delta, PostgreSQL 15+ WHEN MATCHED … WHEN NOT MATCHED …PostgreSQL, SQLiteINSERT … ON CONFLICT (id)needs a unique index on id; DO UPDATE SET city = EXCLUDED.cityDO NOTHING skips duplicatesMySQLINSERT … ON DUPLICATE KEY UPDATE …fires on any unique keyDelta Lake, SQL ServerWHEN NOT MATCHED BY SOURCE THEN DELETEremoves target rows the batch lacksDelta LakeWHEN MATCHED THEN UPDATE SET *all columns; schema can evolveWHEN NOT MATCHED THEN INSERT *Two source rows for the same key (id 2 → Bath, id 2 → Hull), as PostgreSQL 16 answers:ERROR: MERGE command cannot affect row a second timeHINT: Ensure that not more than one source row matches any one target row.Every engine has an upsert, and MERGE fails when two source rows match one target row.Delta Lake and Snowflake raise the same kind of error (Snowflake only while ERROR_ON_NONDETERMINISTIC_MERGE is true, itsdefault). Keep the latest row per key with ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1 before merging.Put a selective condition in ON, such as a date range, so the engine can prune the target instead of scanning all of it tolook for matches.

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.

Open in a new tab