# /// script # requires-python = ">=3.12,<3.15" # dependencies = [ # "marimo[sql]", # "duckdb", # ] # /// """SQL Handbook, Chapter C: More Than One Table. """ import marimo __generated_with = "0.24.0" app = marimo.App(width="medium", sql_output="polars") @app.cell def _(): import duckdb import marimo as mo return duckdb, mo @app.cell(hide_code=True) def _(mo): mo.md(r""" # More Than One Table Chapter C of the SQL Handbook. Seven sections, each one concept. Chapter B asked one table a question. A database splits a business across many tables on purpose and puts a key in each one, so almost every real question needs two of them or more. This chapter is how you put them back together, and what happens to your numbers when you do. Sections 20 and 23 are marked `> **Advanced**`. Nothing in this course requires them. Every query below runs against the course warehouse, Northwind. You can read this straight through, or open it when you need one thing. Every section ends with questions that have no answers written down, because those are the ones asked out loud in class. To run the code yourself, download the `.py` into your project and open it with `uv run marimo edit`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Opening the warehouse The cell below loads the parquet files in `data/northwind/` into DuckDB as tables. How that works is Part D, sections 26 and 27, and nothing in this chapter depends on reading it. One name matters from the first query. The file is `order-details.parquet` and the table is `"Order Details"`, with a space in it, so every mention of it needs double quotes. """) return @app.cell def _(duckdb, mo): # Look for a data/northwind folder here and in every folder above, so the # chapter works wherever you save it. If there is none, read the copy on the # course site instead. _here = mo.notebook_dir() _source = next( (p / "data" / "northwind" for p in [_here, *_here.parents] if (p / "data" / "northwind").is_dir()), "https://oim7510.github.io/data/northwind", ) con = duckdb.connect() _files = { "categories": "categories", "customers": "customers", "employees": "employees", "employeeterritories": "employeeterritories", "Order Details": "order-details", "orders": "orders", "products": "products", "regions": "regions", "shippers": "shippers", "suppliers": "suppliers", "territories": "territories", } for _table, _file in _files.items(): con.execute( f"""CREATE TABLE "{_table}" AS SELECT * FROM '{_source}/{_file}.parquet'""" ) return (con,) @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 17. `INNER JOIN` A join takes two tables and one condition and returns the rows where the condition is true. `INNER JOIN` is the plain version. A row comes out only when it found a partner on the other side. `"Order Details"` holds a `ProductID` and no product name. `products` holds the name and knows nothing about orders. The question "what was on order 10248" needs both tables. 📖 **Used in:** Combining tables with `JOIN` """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Joining on a condition Order 10248 has three lines. Each line names a `ProductID`, and the name behind that id lives in `products`. """) return @app.cell def _(con): con.sql(""" SELECT d."OrderID", p."ProductName", d."Quantity", d."UnitPrice" FROM "Order Details" AS d INNER JOIN products AS p ON d."ProductID" = p."ProductID" WHERE d."OrderID" = 10248 """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" `FROM` names the left table, `INNER JOIN` names the right one, and `ON` gives the condition. Three lines go in and three rows come out, each one carrying columns from both tables. The word `INNER` is optional in every engine this course touches. `JOIN` on its own means the same thing, and you will read far more code that writes it that way. 👉 **Try it:** run the same query for order 10249 and count the rows. Then swap the two tables, putting `products` after `FROM` and `"Order Details"` after `JOIN`, and confirm you get the same rows. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Every column name is qualified `d` and `p` are aliases, and once a table has one, every column of that table is written `alias."Column"`. This is not decoration. Both tables in the query below have a column called `OrderID`, and asking for it without specifying which one is an error. ```sql SELECT "OrderID" FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" ``` ``` Binder Error: Ambiguous reference to column name "OrderID" (use: "o.OrderID" or "d.OrderID") ``` The database refuses because two answers are available and it will not choose for you. **The trap.** A column that exists in only one of the two tables runs fine with no prefix. On the day somebody adds a column of that name to the other table, a query that worked for a year starts failing, and the change is nowhere near it. Prefix every column in a join, including the ones that do not need it yet. 👉 **Try it:** run the ambiguous query above and read the message. Fix it with `o."OrderID"`, then with `d."OrderID"`, and confirm the two give the same rows. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The condition and the output are separate The columns you join on and the columns you select are two different decisions. `CustomerID` appears twice in the `ON` line below and nowhere in the result. """) return @app.cell def _(con): con.sql(""" SELECT c."CompanyName", o."OrderID", round(o."Freight", 2) AS freight FROM orders AS o JOIN customers AS c ON o."CustomerID" = c."CustomerID" WHERE o."OrderID" BETWEEN 10248 AND 10252 ORDER BY o."OrderID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" There are five orders, five company names, and no sign of the key that found them. `VINET` is how order 10248 reached Vins et alcools Chevalier, and nobody reading the output has to know that. 👉 **Try it:** add `o."CustomerID"` to the select list and confirm the row count does not change. Then delete the `ORDER BY` and run it twice. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A row with no partner does not come out `territories` has 53 rows. `employeeterritories` records which employee covers which territory, and it has 49 rows, at most one per territory. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM territories) AS territories, (SELECT count(*) FROM employeeterritories) AS assignments, (SELECT count(*) FROM territories AS t JOIN employeeterritories AS et ON t."TerritoryID" = et."TerritoryID" ) AS rows_from_the_join """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" There are 53 territories going in and 49 rows coming out. Four territories have nobody assigned to them, an inner join has nothing to pair them with, and they leave the result without a word. **The trap.** An inner join answers a question about the rows that matched, and the report it feeds will look complete. Count the input and count the output every time, and when the two differ, find out what fell out before you send the number anywhere. 👉 **Try it:** run each of the three counts on its own. Then write the join out in full and add `count(DISTINCT t."TerritoryID")` to confirm that 49 different territories survive it. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `"Order Details"` needs its quotes ```sql SELECT * FROM Order Details LIMIT 3 ``` ``` Parser Error: syntax error at or near "Order" LINE 1: SELECT * FROM Order Details LIMIT 3 ^ ``` The parser reads `Order` as the table name and then finds a second word where it expected a comma or a clause. Double quotes tell it the space belongs to the name. Every other table in this warehouse is one word and would run unquoted. This course quotes every identifier anyway, because the column names here are `CamelCase` and several engines fold an unquoted name to lower case before looking it up. 👉 **Try it:** run the unquoted version and read the message. Then run `SELECT count(*) FROM "Order Details"` and confirm 609283. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A comma join with no condition An older form lists the tables separated by commas and puts the join condition in `WHERE`. It is valid SQL, it predates `JOIN ... ON`, and DuckDB accepts it. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM orders AS o, "Order Details" AS d WHERE o."OrderID" = d."OrderID" AND o."OrderID" BETWEEN 10248 AND 10252 ) AS condition_present, (SELECT count(*) FROM orders AS o, "Order Details" AS d WHERE o."OrderID" BETWEEN 10248 AND 10252 AND d."OrderID" BETWEEN 10248 AND 10252 ) AS condition_forgotten """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" **The trap.** Leaving the join condition out of a comma join is legal. Every row on the left is paired with every row on the right, five times 14 is 70, and nothing warns you. That result is called a cross join, or a **Cartesian product**, and here it arrived by accident. Write `JOIN ... ON` and the condition has a slot of its own, and leaving it out is a syntax error. ```sql SELECT * FROM orders AS o JOIN "Order Details" AS d LIMIT 3 ``` ``` Parser Error: syntax error at or near "LIMIT" LINE 1: SELECT * FROM orders AS o JOIN "Order Details" AS d LIMIT 3 ^ ``` 👉 **Try it:** run both comma versions and compare 14 with 70. Then write the second one with `JOIN` and no `ON` clause and read the error you get. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### ✔ Check your understanding 1. Your join returns 200 rows. What two numbers do you need before you can judge whether that is the right answer? 2. Two tables in your query both have a `CustomerID` column and you asked for `"CustomerID"` with no prefix. What does the database do, and what would be worse about it guessing? 3. You join `orders` to `customers` and get fewer rows than `orders` has. What has to be true of the data for that to happen? 4. A colleague sends you a query with two table names separated by a comma and a `WHERE` clause holding three conditions. What is the first thing you look for? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 18. `LEFT JOIN`, and what it preserves An inner join reports on rows that matched. A `LEFT JOIN` keeps every row of the left table whether or not it found a partner, and fills the right side with `NULL` where there was none. Its full name is **left outer join**, and `OUTER` is optional the way `INNER` is. The four territories section 17 silently dropped are the reason this exists. 📖 **Used in:** Combining tables with `JOIN` """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Every row of the left table survives The query below is the previous one with `JOIN` changed to `LEFT JOIN`. """) return @app.cell def _(con): con.sql(""" SELECT count(*) AS rows_out, count(DISTINCT t."TerritoryID") AS territories_seen FROM territories AS t LEFT JOIN employeeterritories AS et ON t."TerritoryID" = et."TerritoryID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" There are 53 territories going in and 53 rows coming out, covering all 53. The word `LEFT` names the table that is protected, which is the one written first. 👉 **Try it:** change `LEFT JOIN` back to `JOIN` and confirm the same query gives 49. Then change it to `RIGHT JOIN` and state, before you run it, which table is protected now and what number you expect. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The unmatched side arrives as `NULL` Region 4 has eight territories and Janet Leverling covers four of them. """) return @app.cell def _(con): con.sql(""" SELECT t."TerritoryID", t."TerritoryDescription", et."EmployeeID" FROM territories AS t LEFT JOIN employeeterritories AS et ON t."TerritoryID" = et."TerritoryID" WHERE t."RegionID" = 4 ORDER BY t."TerritoryID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Atlanta, Savannah, Orlando and Tampa carry employee 3. Columbia, Bentonville, Dallas and Austin carry `NULL`, and every column that would have come from `employeeterritories` is `NULL` on those rows. 👉 **Try it:** add `et."TerritoryID"` to the select list and look at what it holds on the Bentonville row. That column appears in the `ON` clause, so it looks like it has to have a value. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Finding what has no match at all Keep every left row, then keep only the ones whose right side came back empty. The result is everything with no partner. That shape is called an anti-join. """) return @app.cell def _(con): con.sql(""" SELECT t."TerritoryID", t."TerritoryDescription", t."RegionID" FROM territories AS t LEFT JOIN employeeterritories AS et ON t."TerritoryID" = et."TerritoryID" WHERE et."TerritoryID" IS NULL ORDER BY t."TerritoryID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Four territories come back, all of them in region 4, and nobody is assigned to any of them. That is a business fact the inner join could not have shown you. Test a column from the right table, and pick one that can never be `NULL` in its own right. The key you joined on is the safe choice. Testing a nullable column such as `et."EmployeeID"` would also catch rows that matched and happened to have no value there. 👉 **Try it:** write the anti-join the other way around, from `employeeterritories` out to `territories`, looking for assignments that point at a territory that does not exist. State what a nonzero answer would mean before you run it. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `count(*)` counts a row that matched nothing A row that found no partner is still a row. """) return @app.cell def _(con): con.sql(""" SELECT t."TerritoryID", t."TerritoryDescription", count(*) AS with_star, count(et."EmployeeID") AS with_column FROM territories AS t LEFT JOIN employeeterritories AS et ON t."TerritoryID" = et."TerritoryID" WHERE t."RegionID" = 4 GROUP BY t."TerritoryID", t."TerritoryDescription" ORDER BY t."TerritoryID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Bentonville has nobody assigned, and `count(*)` reports one assignment for it. `count(*)` counts rows, and the `LEFT JOIN` produced a row. `count(et."EmployeeID")` counts values, `NULL` is not a value, and it reports zero. **The trap.** "How many employees cover each territory" written with `count(*)` on a `LEFT JOIN` gives every empty territory a coverage of one. The number is plausible, the query runs, and the four towns with no salesperson disappear into the average. 👉 **Try it:** run both counts over all 53 territories with no `GROUP BY` and no `WHERE`, and check the two totals against 53 and 49. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A right-table filter in `WHERE` undoes the `LEFT` Both queries below are `LEFT JOIN`s asking about employee 3. One puts the condition in `WHERE` and the other puts it in `ON`. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM territories AS t LEFT JOIN employeeterritories AS et ON t."TerritoryID" = et."TerritoryID" WHERE et."EmployeeID" = 3 ) AS filtered_in_where, (SELECT count(*) FROM territories AS t LEFT JOIN employeeterritories AS et ON t."TerritoryID" = et."TerritoryID" AND et."EmployeeID" = 3 ) AS filtered_in_on """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The `ON` clause decides which right rows are allowed to match, and the left row survives either way, so the second query keeps all 53. `WHERE` runs after the join and throws rows away. Every unmatched left row holds `NULL` in `et."EmployeeID"`, and `NULL = 3` is unknown, so all 49 of the protected rows vanish and four remain. **The trap.** A `LEFT JOIN` whose `WHERE` clause tests a right-table column returns exactly what an inner join would return. Any condition on the right table belongs in `ON`. The one exception is the `IS NULL` test in the anti-join above, which is a question about the join itself. 👉 **Try it:** take the anti-join from the previous point and move `et."TerritoryID" IS NULL` from `WHERE` into the `ON` clause. Count the rows and explain what the query now means. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `NULL` values that print the same A `NULL` from a `LEFT JOIN` means no matching row existed. A `NULL` in a column means the row exists and the value was never recorded. Both show as `NULL`. """) return @app.cell def _(con): con.sql(""" SELECT count(*) AS orders, count("ShippedDate") AS shipped, count(*) - count("ShippedDate") AS never_shipped FROM orders """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" There are 21 orders with no `ShippedDate`. No join was involved. `orders` is one table, the rows are there, and the value is missing, which is section 9's kind of `NULL`. Telling the two apart is a question about where you are looking. A `NULL` that came from a join disappears when you query the right table on its own; a `NULL` in the row is still there. 👉 **Try it:** list those 21 orders with their customer's company name, using a join. Then state which kind of `NULL` you would be looking at if an order carried a `CustomerID` that no customer had. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### ✔ Check your understanding 1. You have a `LEFT JOIN` and the result has the same number of rows as the left table. What does that tell you, and what does it fail to tell you? 2. A monthly report counts customers per sales region with `count(*)` over a `LEFT JOIN`. What number does an empty region show, and what would you write instead? 3. Somebody moves a condition from `ON` to `WHERE` in a `LEFT JOIN` and the row count drops. Explain what happened without using the word "outer". 4. You want every territory with the employee covering it, including the ones nobody covers. Which table goes on the left, and what number tells you afterward that the uncovered ones are in your result? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 19. When a `JOIN` duplicates rows A join pairs rows. When one row on the left matches three rows on the right, that left row comes out three times, and every column it carries comes out three times with it. That is a **fan-out**. Nothing in the syntax mentions this. The query runs, the result still looks like a table of orders, and any total taken from a left-table column is too big. 📖 **Used in:** Combining tables with `JOIN` """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Count the rows before and after The five orders 10248 through 10252 are joined to their lines below. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM orders WHERE "OrderID" BETWEEN 10248 AND 10252 ) AS orders_alone, (SELECT count(*) FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 ) AS after_the_join """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Five orders go in and 14 rows come out. The join added nine rows and no new order. 👉 **Try it:** run the join itself without the `count(*)`, read the 14 rows, and write down how many times `10248` appears in the `OrderID` column. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Where the 14 comes from One row of `"Order Details"` is one product on one order. """) return @app.cell def _(con): con.sql(""" SELECT o."OrderID", round(o."Freight", 2) AS freight, count(*) AS n_lines FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 GROUP BY o."OrderID", o."Freight" ORDER BY o."OrderID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The line counts are three, two, three, three and three, and those add to 14. Before the join, one row was one order. After it, one row is one order line, and the order's own columns have been copied onto each of them. The freight of 16.75 sits on all three rows of order 10248. **The grain of a result is the grain of the finest table in it.** Grain is also called **granularity**. Name that grain out loud before you aggregate anything. 👉 **Try it:** look at the `freight` and `n_lines` columns side by side and predict what `sum(o."Freight")` over the 14 rows would give for order 10248 alone. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The number that goes wrong """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM orders WHERE "OrderID" BETWEEN 10248 AND 10252 ) AS rows_before, (SELECT round(sum("Freight"), 2) FROM orders WHERE "OrderID" BETWEEN 10248 AND 10252 ) AS freight_before, (SELECT count(*) FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 ) AS rows_after, (SELECT round(sum(o."Freight"), 2) FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 ) AS freight_after """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The five orders carry 120.50 in freight. Joined to their lines, the same sum reports 339.25. Check it by hand. Multiply each order's freight by its line count and add the five products together. ``` 16.75 x 3 = 50.25 22.25 x 2 = 44.50 25.00 x 3 = 75.00 20.25 x 3 = 60.75 36.25 x 3 = 108.75 ------ 339.25 ``` The result is about 2.8 times the real figure. Nothing about it looks broken. 👉 **Try it:** do that arithmetic yourself and confirm 339.25. Then drop 10249, the order with two lines, out of the range and predict both totals before you rerun. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The same mistake on the whole warehouse """) return @app.cell def _(con): con.sql(""" SELECT (SELECT round(sum("Freight"), 2) FROM orders) AS freight_in_orders, (SELECT round(sum(o."Freight"), 2) FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" ) AS freight_after_join """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Total freight in `orders` is 4047470.50. The joined version says 206911676.00, about 51 times as large. The average order here carries 37 lines, and the factor lands above that because the orders with the most freight also carry the most lines. A number that large is loud enough to catch. The five-order version is not, and neither is the same error run over one quarter or one sales region, which produces a figure somebody will put on a slide. 👉 **Try it:** run both totals restricted to orders placed in 2016, using `WHERE "OrderDate" >= DATE '2016-01-01' AND "OrderDate" < DATE '2017-01-01'`, and work out the ratio between them. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Which columns survive a fan-out """) return @app.cell def _(con): con.sql(""" SELECT (SELECT sum("Quantity") FROM "Order Details" WHERE "OrderID" BETWEEN 10248 AND 10252 ) AS units_from_details, (SELECT sum(d."Quantity") FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 ) AS units_after_join """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Both say 282. `Quantity` lives on `"Order Details"`, which is the table the joined result is at the grain of, so each value appears exactly once and the total is untouched. `Freight` lives on `orders`, one level up, and gets copied. So the rule is about where the column lives. A column from the many side is safe to add up after the join. A column from the one side is not. So a query that joins order lines to products and sums a quantity is correct, because quantity is on the many side. Copy that pattern, swap in an order-level column, and you get a wrong number and no warning. 👉 **Try it:** pick two more columns, one from each table, and predict for each whether a `sum` over the join matches a `sum` over its own table. Then check both. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The fix that passes the small test `SUM(DISTINCT ...)` is the first repair almost anybody reaches for, including an agent. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT round(sum(DISTINCT o."Freight"), 2) FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 ) AS distinct_on_five_orders, (SELECT round(sum(DISTINCT o."Freight"), 2) FROM orders AS o JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" ) AS distinct_on_everything, (SELECT round(sum("Freight"), 2) FROM orders) AS the_real_total """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" On the five orders it returns 120.50, which is right. Across the warehouse it returns 594656.00 against a true 4047470.50. `SUM(DISTINCT ...)` removes duplicate **values**, and the join duplicated **rows**. The five orders happen to carry five different freight amounts, so the two ideas agree. Across 16282 orders there are only 2140 different freight amounts, so every order sharing an amount with another one is thrown away along with the copies. **The trap.** The test that would have caught this is the one already run, and it passed. Check a repair against a range wide enough to hold a repeated value, and against the total you are trying to reproduce. 👉 **Try it:** find two orders with the same freight (`GROUP BY "Freight" HAVING count(*) > 1`), add them to a range, and watch `sum(DISTINCT ...)` lose one of them. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The fix that works Collapse the many side to one row per order first. Then join. """) return @app.cell def _(con): con.sql(""" SELECT o."OrderID", round(o."Freight", 2) AS freight, lines.n_lines, lines.units FROM orders AS o JOIN ( SELECT "OrderID", count(*) AS n_lines, sum("Quantity") AS units FROM "Order Details" GROUP BY "OrderID" ) AS lines ON o."OrderID" = lines."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 ORDER BY o."OrderID" """) return @app.cell def _(con): con.sql(""" SELECT count(*) AS rows_out, round(sum(o."Freight"), 2) AS total_freight FROM orders AS o JOIN ( SELECT "OrderID", count(*) AS n_lines FROM "Order Details" GROUP BY "OrderID" ) AS lines ON o."OrderID" = lines."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The result is five rows and 120.50. The right side now holds one row per `OrderID`, so no order can be duplicated, and the line counts and unit totals are still there. That parenthesized query is a subquery in `FROM`, which is section 21. Section 22 writes the same thing with a name in front of it, and that is the form you will actually use. 👉 **Try it:** change the inner query's `GROUP BY` to `"OrderID", "ProductID"` and rerun both cells. Count the rows and explain which number moved and why. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### ✔ Check your understanding 1. A join between two tables returns more rows than either table holds. What do you now know about the data? 2. You joined orders to order lines and summed a column. What single question decides whether that total is right? 3. Your revenue figure is 2.8 times what finance reports. Name two checks you would run before reporting the figure to anybody. 4. Somebody hands you a query with `SUM(DISTINCT)` in it. What do you ask them? 5. A colleague argues the join is fine because the row count went from five to 14 and 14 is not a multiple of five. What is wrong with that argument? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 20. Self joins and multi-table joins > **Advanced** Nothing in this course requires this section. A self join reads a relationship that is stored inside one table. A multi-table join follows keys through three or four tables in a single statement. Both are ordinary joins, and both make section 19's row multiplication easy to walk into. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A table joined to itself `employees` has a column `ReportsTo` holding another employee's `EmployeeID`. To print a manager's name beside an employee's name, the table has to appear twice, and the aliases are the only thing keeping the two copies apart. """) return @app.cell def _(con): con.sql(""" SELECT e."EmployeeID", e."FirstName" || ' ' || e."LastName" AS employee, e."ReportsTo", m."FirstName" || ' ' || m."LastName" AS manager FROM employees AS e LEFT JOIN employees AS m ON e."ReportsTo" = m."EmployeeID" ORDER BY e."EmployeeID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Nine people come back, eight of them with a named manager, and Andrew Fuller carries `NULL` because nobody is above him. Two things check this query. The count matches the table, and exactly one row has no manager. The join has to be a `LEFT JOIN` for the boss to appear at all. 👉 **Try it:** count the rows with `LEFT JOIN` and again with `JOIN`, and state which person you lose. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The condition has to name the key """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM employees) AS employees, (SELECT count(*) FROM employees AS e JOIN employees AS m ON e."ReportsTo" = m."EmployeeID" ) AS inner_join, (SELECT count(*) FROM employees AS e LEFT JOIN employees AS m ON e."ReportsTo" = m."EmployeeID" ) AS left_join, (SELECT count(*) FROM employees AS e JOIN employees AS m ON e."ReportsTo" = m."ReportsTo" ) AS joined_on_the_wrong_column """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The last one runs, raises nothing, and returns 34 rows from a table of nine. Joining on `ReportsTo = ReportsTo` pairs every employee with every colleague who shares a manager, including themselves. **The trap.** A self join whose `ON` clause names something other than the key is a fan-out. Joining employees to employees `ON e.OrganizationLevel = m.OrganizationLevel + 1` looks like a way to walk a hierarchy, and it pairs every employee with every employee one level above, so a company with 20 managers on a level gives each of their reports 20 managers. 👉 **Try it:** run the wrong version in full, without `count(*)`, and find the rows where an employee is their own colleague. Then add `AND e."EmployeeID" <> m."EmployeeID"` and count again. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Adding tables a `JOIN` at a time Every `JOIN` after the first brings a table and an `ON` clause with it. The query below reaches `customers`, `"Order Details"`, `products` and `employees` from `orders`. """) return @app.cell def _(con): con.sql(""" SELECT c."CompanyName", p."ProductName", d."Quantity", round(d."UnitPrice", 2) AS unit_price, e."LastName" AS sales_rep FROM orders AS o JOIN customers AS c ON o."CustomerID" = c."CustomerID" JOIN "Order Details" AS d ON o."OrderID" = d."OrderID" JOIN products AS p ON d."ProductID" = p."ProductID" JOIN employees AS e ON o."EmployeeID" = e."EmployeeID" WHERE o."OrderID" = 10248 """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The result is three rows, one per order line. Every join here climbs from a foreign key to a primary key, which matches at most one row, so nothing multiplies after the first join to `"Order Details"`. 👉 **Try it:** delete the `WHERE` clause and count the rows. Compare that number with `count(*)` on `"Order Details"` and explain why they agree. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Child tables that multiply each other Steven Buchanan is employee 5. He has orders, and he covers territories, and those two facts have nothing to do with each other. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM orders WHERE "EmployeeID" = 5) AS his_orders, (SELECT count(*) FROM employeeterritories WHERE "EmployeeID" = 5 ) AS his_territories, (SELECT count(*) FROM employees AS e JOIN orders AS o ON e."EmployeeID" = o."EmployeeID" JOIN employeeterritories AS et ON e."EmployeeID" = et."EmployeeID" WHERE e."EmployeeID" = 5 ) AS joined_to_both """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" There are 1804 orders and seven territories, and the join returns 12628 rows, which is 1804 times 7. Every order is paired with every territory. Any total of order freight taken from that result is seven times too large, and any count of territories is 1804 times too large. Two independent children of the same parent multiply each other. Section 19 is one table fanning out; this is two, and the factor is the product. 👉 **Try it:** run the same three counts for employee 7, who covers 10 territories, and predict the third number before you look. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### ✔ Check your understanding 1. Why does a self join need aliases when a two-table join can sometimes get away without them? 2. Your hierarchy query returns more rows than the table has people. What is the first thing you check in the `ON` clause? 3. A four-table join returns the same row count as the largest of the four tables. What does that tell you about the three joins? 4. You need each salesperson's total order value and the number of territories they cover. Explain why one query with two joins gives the wrong answer, and describe a shape that gives the right one. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 21. Subqueries A subquery is a `SELECT` written inside another statement. It runs, and the query around it uses the result. Where it sits decides what it is allowed to return: one value in most of a `WHERE` clause, a column of values in front of `IN`, and a whole table in `FROM`. 📖 **Used in:** Combining tables with `JOIN` """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A subquery that returns a single value `WHERE "UnitPrice" > avg("UnitPrice")` is not allowed, because `WHERE` runs before any aggregate exists (section 16). A subquery gets the average from its own separate pass over the table, so the value is ready before the outer `WHERE` needs it. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT round(avg("UnitPrice"), 2) FROM products) AS average_price, (SELECT count(*) FROM products) AS all_products, (SELECT count(*) FROM products WHERE "UnitPrice" > (SELECT avg("UnitPrice") FROM products) ) AS above_average """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The average price is 28.87 and 25 of the 77 products sit above it. A subquery in this position, a **scalar subquery**, has to return one row and one column. Two rows is a runtime error, so this is one mistake the database catches for you. 👉 **Try it:** list those 25 products, most expensive first. Then change `avg` to `median` and count again, and state which of the two numbers you would quote to somebody asking about a typical price. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A subquery that returns a list `IN` takes a column of values, and a subquery can supply it. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM orders WHERE "CustomerID" IN ( SELECT "CustomerID" FROM customers WHERE "Country" = 'France' ) ) AS with_in, (SELECT count(*) FROM orders AS o JOIN customers AS c ON o."CustomerID" = c."CustomerID" WHERE c."Country" = 'France' ) AS with_a_join """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Both say 1909. The join and the `IN` answer the same question, and they part company in two places. The join can put `c."CompanyName"` in the output and `IN` cannot. And a join to a table where the key repeats duplicates rows, which is section 19, while `IN` never does. An `IN` subquery like this is a **semi-join**. 👉 **Try it:** add `c."CompanyName"` to the join version, then try to add it to the `IN` version. Then check with `count(DISTINCT ...)` that the 1909 orders come from 11 French customers. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `NOT IN` and a `NULL` in the list The question is which employees manage nobody. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM employees WHERE "EmployeeID" NOT IN (SELECT "ReportsTo" FROM employees) ) AS manages_nobody, (SELECT count(*) FROM employees WHERE "EmployeeID" NOT IN ( SELECT "ReportsTo" FROM employees WHERE "ReportsTo" IS NOT NULL ) ) AS manages_nobody_fixed """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The first answer is zero and the correct answer is seven. `ReportsTo` is `NULL` for Andrew Fuller, so the list the subquery hands over is `2, NULL, 2, 2, 2, 5, 5, 2, 5`. `x NOT IN (...)` is true only when `x` differs from every entry, comparing anything with `NULL` gives unknown (section 9), and unknown is not true. So the test never passes and every row is filtered out. **The trap.** Zero rows is a perfectly plausible business answer. Nothing here distinguishes "no employee manages anybody" from "this query cannot return a row". Any `NOT IN` over a column that can hold `NULL` needs `IS NOT NULL` inside the subquery, or `NOT EXISTS`, or the anti-join from section 18. 👉 **Try it:** list the seven with the fixed version. Then write the same question as a `LEFT JOIN` anti-join, `employees` to `employees` on `e."EmployeeID" = r."ReportsTo"`, and confirm you get the same seven. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A subquery in `FROM` is a table Section 19's fix is this shape. Aggregate the many side down to one row per key, then join to it. """) return @app.cell def _(con): con.sql(""" SELECT c."CompanyName", round(t.freight, 2) AS freight, t.n_orders FROM ( SELECT "CustomerID", sum("Freight") AS freight, count(*) AS n_orders FROM orders GROUP BY "CustomerID" ) AS t JOIN customers AS c ON t."CustomerID" = c."CustomerID" ORDER BY t.freight DESC LIMIT 5 """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" B's Beverages tops the list at 54797.25 across 210 orders. Every column the subquery exposes has to be named, which is why `sum("Freight")` carries `AS freight`. DuckDB lets you leave the alias `AS t` off the subquery itself; several other engines refuse, so write it. 👉 **Try it:** delete `AS t` and run it. DuckDB accepts the subquery without a name, and the error says `Referenced table "t" not found!`, because the rest of the query lost the name it was using. Put `AS t` back, move the `LIMIT 5` inside the subquery, run it, and explain why the five companies changed. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### ✔ Check your understanding 1. A subquery in a `WHERE` clause returns two rows and the query fails. Where else could that same subquery sit without failing? 2. When does `IN` with a subquery give a different row count from the equivalent join? 3. `NOT IN` returns zero rows and you were expecting a handful. What do you check, and what is the one-line fix? 4. You have a subquery in `FROM` that groups order lines by `OrderID`. What has to be true of its output for the join around it to be safe? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 22. Common table expressions (`WITH`) `WITH name AS (query)` puts a subquery in front of the statement and gives it a name. The named query is a **common table expression**, abbreviated CTE. Everything after it can read that name as if it were a table. The result is the same as a subquery in `FROM`, and the query reads from the top down in the order the work happens. 📖 **Used in:** Combining tables with `JOIN` """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `WITH` names a step This is section 19's fix with the parentheses moved to the front. """) return @app.cell def _(con): con.sql(""" WITH lines AS ( SELECT "OrderID", count(*) AS n_lines, sum("Quantity") AS units FROM "Order Details" GROUP BY "OrderID" ) SELECT o."OrderID", round(o."Freight", 2) AS freight, lines.n_lines, lines.units FROM orders AS o JOIN lines ON o."OrderID" = lines."OrderID" WHERE o."OrderID" BETWEEN 10248 AND 10252 ORDER BY o."OrderID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The five rows, the freight and the line counts are the same as before. The name `lines` goes wherever a table name would go, and the reader meets the definition before the query that uses it. A `WITH` clause can be read on its own. Replacing the whole final `SELECT` with `SELECT * FROM lines LIMIT 5` is how you check a step in the middle of a query you are debugging. 👉 **Try it:** run the total version as a CTE and confirm five rows and 120.50. Then replace the final select with `SELECT * FROM lines LIMIT 5` and read what the step produced. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The same question written both ways """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM orders WHERE "Freight" > (SELECT avg("Freight") FROM orders) ) AS with_a_subquery, (WITH avg_freight AS (SELECT avg("Freight") AS f FROM orders) SELECT count(*) FROM orders JOIN avg_freight ON orders."Freight" > avg_freight.f ) AS with_a_cte """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Both give 8025 orders above the average freight of 248.59. The subquery mentions nothing outside itself, so both versions compute the average once. What the CTE adds is that the step has a name and somebody can read it. 👉 **Try it:** time both with `SELECT count(*)` over the full 16282 orders and see whether you can measure a difference. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A correlated subquery "Products priced above the average for their own category" needs a different average for each row, and the subquery mentions `p."CategoryID"` from the outer query to get it. A subquery that mentions a column of the outer query is **correlated**. Read literally, it runs once for every outer row. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM products AS p WHERE p."UnitPrice" > ( SELECT avg(p2."UnitPrice") FROM products AS p2 WHERE p2."CategoryID" = p."CategoryID" ) ) AS correlated_subquery, (WITH category_avg AS ( SELECT "CategoryID", avg("UnitPrice") AS avg_price FROM products GROUP BY "CategoryID" ) SELECT count(*) FROM products AS p JOIN category_avg AS a ON p."CategoryID" = a."CategoryID" WHERE p."UnitPrice" > a.avg_price ) AS cte_and_join """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Both say 27. Read literally, the first computes an average for each of the 77 products and the second computes eight averages and joins to them. DuckDB does not run the first one literally. It rewrites it into the second shape, one grouping and one join, before it runs. SQLite does run it once per row. The CTE version spells out the efficient shape, so it does not depend on the engine finding it. The join in the second version is safe from section 19 because `category_avg` holds one row per `CategoryID`, which is the same property that made the fix work there. 👉 **Try it:** run the CTE on its own and confirm it returns eight rows. Then list the 27 products with their category and their category average side by side. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A chain of CTEs One `WITH` can define several names, separated by commas. The second may read the first. """) return @app.cell def _(con): con.sql(""" WITH lines AS ( SELECT "ProductID", sum("Quantity") AS units FROM "Order Details" GROUP BY "ProductID" ), by_category AS ( SELECT c."CategoryName", sum(l.units) AS units FROM lines AS l JOIN products AS p ON l."ProductID" = p."ProductID" JOIN categories AS c ON p."CategoryID" = c."CategoryID" GROUP BY c."CategoryName" ) SELECT "CategoryName", units FROM by_category ORDER BY units DESC """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Confections leads on 2628466 units, and Produce trails on 1010224. The query has three steps and each one carries a name: total by product, roll up to category, sort. Each name is checkable on its own by changing the final select, which is the practical reason to write a long query this way. 👉 **Try it:** replace the last select with `SELECT * FROM lines LIMIT 5` and check that the units for one product match `sum("Quantity")` on `"Order Details"` for that product. Then add a third step keeping only categories above 2000000 units. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### ✔ Check your understanding 1. A colleague says a CTE is always faster than a subquery. When is that true and when is it not? 2. Your query has four CTEs and returns a number you do not believe. Describe how you find the step that is wrong. 3. What makes a subquery correlated, and how do you spot one by reading it? 4. You write a CTE that aggregates order lines by `OrderID` and join it to `orders`. What property of that CTE makes the join safe, and how would you check it? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 23. Window functions > **Advanced** Nothing in this course requires this section. A window function keeps every row and adds a column computed across a set of rows. `GROUP BY` collapses rows into groups. When one question needs the row-level detail and a group-level number in the same result, a window function gives both. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `OVER ()` looks at every row An empty `OVER ()` means the window is the whole result. """) return @app.cell def _(con): con.sql(""" SELECT c."CategoryName", count(*) AS n_products, sum(count(*)) OVER () AS all_products, round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct FROM products AS p JOIN categories AS c ON p."CategoryID" = c."CategoryID" GROUP BY c."CategoryName" ORDER BY n_products DESC """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Confections holds 13 of the 77 products, which is 16.9 percent. The `GROUP BY` runs first and produces eight rows, and the window function then sums across those eight, so `all_products` reads 77 on every row. Section 21 answers the same question with a scalar subquery. The window form takes one pass and puts the share beside the count. 👉 **Try it:** delete `OVER ()` and read the error. Then change it to `OVER (PARTITION BY c."CategoryName")` and explain why every percentage becomes 100.0. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `PARTITION BY` restarts the window The query below keeps the two most expensive products in each category. """) return @app.cell def _(con): con.sql(""" SELECT c."CategoryName", p."ProductName", round(p."UnitPrice", 2) AS price, RANK() OVER ( PARTITION BY c."CategoryName" ORDER BY p."UnitPrice" DESC ) AS rank_in_category FROM products AS p JOIN categories AS c ON p."CategoryID" = c."CategoryID" QUALIFY rank_in_category <= 2 ORDER BY c."CategoryName", rank_in_category """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Eight categories give two rows each, which is 16 rows. `PARTITION BY` restarts the ranking at every new category, and the `ORDER BY` inside `OVER` is the ranking rule, which has nothing to do with the `ORDER BY` at the end of the statement. `QUALIFY` filters on a window function's result. DuckDB, Snowflake and BigQuery have it. Elsewhere the query goes inside a CTE and the filter goes in a `WHERE` outside it, because `WHERE` runs before window functions are computed. 👉 **Try it:** rewrite this with a CTE and an outer `WHERE rank_in_category <= 2`, and confirm the same 16 rows. Then put that `WHERE` into the original query and read the error. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `RANK()` and `ROW_NUMBER()` disagree on ties Two products cost 38.00. """) return @app.cell def _(con): con.sql(""" SELECT "ProductName", round("UnitPrice", 2) AS price, RANK() OVER (ORDER BY "UnitPrice" DESC) AS rk, ROW_NUMBER() OVER (ORDER BY "UnitPrice" DESC) AS rn FROM products WHERE "UnitPrice" BETWEEN 32.80 AND 38.00 ORDER BY "UnitPrice" DESC, "ProductName" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Gnocchi di nonna Alice and Queso Manchego La Pastora both cost 38.00. `RANK()` gives them both 1 and then skips to 3. `ROW_NUMBER()` gives one of them 1 and the other 2, and which one gets 1 is arbitrary, because the `ORDER BY` inside `OVER` does not separate them. **The trap.** "Top 10 by revenue" written with `ROW_NUMBER()` always returns exactly 10 rows and drops a tie without saying so; written with `RANK()` it can return 11. Decide which behavior the question wants, and add a tiebreaker column to the `ORDER BY` inside `OVER` when the answer has to be the same every time. 👉 **Try it:** add `, "ProductName"` inside both `OVER` clauses, run it twice, and state which of the two columns stopped moving. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `LAG()` reads the previous row The query below groups freight by month for the second half of 2016 and adds the change from the month before. """) return @app.cell def _(con): con.sql(""" WITH monthly AS ( SELECT date_trunc('month', "OrderDate")::DATE AS month, round(sum("Freight"), 2) AS freight FROM orders WHERE "OrderDate" >= DATE '2016-07-01' AND "OrderDate" < DATE '2017-01-01' GROUP BY 1 ) SELECT month, freight, LAG(freight) OVER (ORDER BY month) AS prev_month, freight - LAG(freight) OVER (ORDER BY month) AS change FROM monthly ORDER BY month """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" August is down 4411.00 on July and December is up 4017.75 on November. Period over period change is the most common business use of a window function. July has no previous month, so `prev_month` and `change` are `NULL` on that row. That is correct, and it will quietly change any average taken over the `change` column. The date range is written half open, from the first of July up to but not including the first of January, which is section 12. 👉 **Try it:** change `LAG` to `LEAD` and state which row goes `NULL` now. Then add `round(100.0 * change / prev_month, 1) AS pct_change` and look at the July row. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A frame decides which rows the window sees A running total needs each row to see everything before it and nothing after it, and the frame clause is where you specify it. """) return @app.cell def _(con): con.sql(""" WITH monthly AS ( SELECT date_trunc('month', "OrderDate")::DATE AS month, round(sum("Freight"), 2) AS freight FROM orders WHERE "OrderDate" >= DATE '2016-07-01' AND "OrderDate" < DATE '2017-01-01' GROUP BY 1 ) SELECT month, freight, sum(freight) OVER ( ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM monthly ORDER BY month """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" `UNBOUNDED PRECEDING` to `CURRENT ROW` means every row from the start of the window up to this one, which is a running total. The last value, 184977.00, is the sum of all six months, and checking it against a plain `sum` over the same range is the verification for this kind of query. 👉 **Try it:** change the frame to `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` and describe in one sentence what the column now holds. Then remove the frame clause entirely and compare. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### ✔ Check your understanding 1. You need each order's freight and the average freight for its customer, in the same row. Explain why `GROUP BY` cannot do this. 2. Your ranking query returns 11 rows when the question asked for 10. Which function is in it, and what would the other one have returned? 3. A monthly growth report shows a blank first row. What is that, and what would you tell the person reading the report? 4. `QUALIFY` is not available in your engine. Describe the rewrite and explain why `WHERE` does not work. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## Chapter challenge Nothing here is due. It exists because each section above teaches one thing, and one real question needs several of them at once. **Answer a business question that needs three tables, and prove the number.** Pick something somebody would actually ask of this warehouse. Which category earns the most freight. Which customers order from more than half the categories. Which products have never been ordered by a customer outside their supplier's country. Your own question is better than any of these, and it has to need at least three tables and at least one aggregate. **Done looks like** one notebook holding four things. 1. The query, with every column qualified by a table alias. 2. A row count before each join and after it, in the notebook, run. 3. One sentence naming the grain of your result. What is one row. 4. A "How I know these numbers are right" paragraph with one independent check that runs. Recomputing a total a second way is the usual one. **Four decisions are yours, and no query will make them.** 1. **What grain does your answer live at?** One row per customer, per order, per order line. The number changes with the answer. 2. **Which join type at each step?** For every `INNER JOIN` you write, state what it drops and how many rows that was. 3. **What happens to the rows with no match?** Count them, then decide whether they are a zero or an absence, and state which. 4. **Where does the aggregate happen?** Before the join or after it. Section 19 is the whole reason this is a decision. **Going further.** Add a window function so the result carries both the detail and the group total, which is section 23. Add a self join if your question has anything reporting to anything else, which is section 20. Then take the same question to a second grain, one row per month instead of one row per customer, and see which of your conclusions survives. And hand the finished notebook to somebody who is not in this course without explaining it, and write down every question they had to ask before they believed the number. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 🤖 Ask your agent Two different uses, and the second one is the one this course grades. ### Ask it about what this chapter left out - `FULL OUTER JOIN` and `RIGHT JOIN` are named nowhere above. Ask what each returns, ask for one business question that genuinely needs a `FULL OUTER JOIN`, and ask which engines support them. - Ask what `USING (ProductID)` does, how it differs from an `ON` clause, and what `NATURAL JOIN` does. Then ask why most style guides ban `NATURAL JOIN`. - Ask how `NOT EXISTS` differs from `NOT IN`, and have it explain what happens to each when the inner query returns a `NULL`. - Ask what `WITH RECURSIVE` is, and have it write the query that prints the whole management chain from Northwind's `employees` table. - Ask what `EXPLAIN` prints in DuckDB, run it on the fan-out query from section 19, and ask it to point out where the row count grows. Test whatever comes back. Run it. ### Watch what it decides without telling you Give it this, exactly: > *"Using the Northwind tables `orders` and `Order Details`, write me a SQL query that > gives total freight by customer."* Then read what came back and answer four questions. 1. **Did it join the two tables?** If it did, count the rows its query returns and compare with 93. 2. **Which number does its query produce for total freight across all customers?** Run it and compare with 4047470.50. 3. **Did it say anything about duplication?** Some agents add a comment about it, some add `DISTINCT`, and some do neither. 4. **What grain did it assume?** You asked for "by customer" and nothing else, and it chose whether that means per customer, per customer per year, or per customer per order. If it got this right the first time, ask it the same question with `employees` and `employeeterritories` added, which is section 20's compounding case. 👉 **Try it:** send the same request twice, in two separate conversations, and put the two queries side by side. Then send it a third time with one extra sentence saying freight is recorded once per order, and see whether the query changes. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## Chapter C checklist - [ ] I can write an inner join between two tables and qualify every column in it - [ ] I can state what an inner join dropped, and count it - [ ] I can state which table a `LEFT JOIN` protects and what arrives as `NULL` - [ ] I can write an anti-join to find rows with no match anywhere - [ ] I can explain why a right-table condition belongs in `ON` and not in `WHERE` - [ ] I can tell a `NULL` from a missing match apart from a `NULL` in the row - [ ] I can count the rows before a join and after it, and explain why the two differ - [ ] I can name the grain of a joined result before I aggregate anything - [ ] I can state which columns are safe to sum after a join and which are not - [ ] I can explain why `SUM(DISTINCT ...)` passes a small test and fails a large one - [ ] I can aggregate the many side first and join to the result - [ ] I can write a subquery in `WHERE`, in `IN` and in `FROM` - [ ] I can explain what `NOT IN` does when the list contains a `NULL` - [ ] I can turn a nested query into a named `WITH` step and check that step on its own **Part D builds a database.** It covers `CREATE TABLE`, `INSERT`, loading a CSV, and running SQL from a notebook, which is how every query in this book reached the page. """) return if __name__ == "__main__": app.run()