Loading the chapter…
%23%20%2F%2F%2F%20script%0A%23%20requires-python%20%3D%20%22%3E%3D3.12%2C%3C3.15%22%0A%23%20dependencies%20%3D%20%5B%0A%23%20%20%20%20%20%22marimo%5Bsql%5D%22%2C%0A%23%20%20%20%20%20%22duckdb%22%2C%0A%23%20%5D%0A%23%20%2F%2F%2F%0A%22%22%22SQL%20Handbook%2C%20Chapter%20B%3A%20Asking%20One%20Table%20a%20Question.%0A%22%22%22%0A%0Aimport%20marimo%0A%0A__generated_with%20%3D%20%220.25.2%22%0Aapp%20%3D%20marimo.App(width%3D%22medium%22%2C%20sql_output%3D%22polars%22)%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20import%20duckdb%0A%20%20%20%20import%20marimo%20as%20mo%0A%0A%20%20%20%20return%20duckdb%2C%20mo%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%20Asking%20One%20Table%20a%20Question%0A%0A%20%20%20%20Chapter%20B%20of%20the%20SQL%20Handbook.%20There%20are%2011%20sections%2C%20each%20one%20concept%2C%20and%20together%0A%20%20%20%20they%20are%20one%20statement.%0A%0A%20%20%20%20Chapter%20A%20gave%20you%20the%20vocabulary.%20This%20chapter%20is%20the%20query.%20By%20%C2%A716%20you%20will%20have%0A%20%20%20%20written%20every%20clause%20of%20the%20longest%20form%20a%20single-table%20query%20takes%2C%20and%20you%20will%0A%20%20%20%20know%20which%20of%20them%20runs%20first.%0A%0A%20%20%20%20Every%20query%20below%20runs%20against%20the%20course%20warehouse%2C%20Northwind%2C%20and%20every%20one%20of%20them%0A%20%20%20%20shows%20the%20rows%20it%20returned.%20You%20can%20read%20this%20straight%20through%2C%20or%20open%20it%20when%20you%0A%20%20%20%20need%20one%20thing.%20Each%20section%20ends%20with%20questions%20that%20have%20no%20answers%20written%20down%2C%0A%20%20%20%20because%20those%20are%20the%20ones%20asked%20out%20loud%20in%20class.%0A%0A%20%20%20%20To%20run%20the%20code%20yourself%2C%20download%20the%20%60.py%60%20into%20your%20project%20and%20open%20it%20with%0A%20%20%20%20%60uv%20run%20marimo%20edit%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Opening%20the%20warehouse%0A%0A%20%20%20%20The%20cell%20below%20loads%20the%2013%20parquet%20files%20in%20%60data%2Fnorthwind%2F%60%20into%20DuckDB%20as%2013%0A%20%20%20%20tables.%20How%20that%20works%20is%20Part%20D%2C%20sections%2026%20and%2027%2C%20and%20nothing%20in%20this%20chapter%0A%20%20%20%20depends%20on%20reading%20it.%0A%0A%20%20%20%20One%20name%20matters%20from%20the%20first%20query.%20The%20file%20is%20%60order-details.parquet%60%2C%20the%0A%20%20%20%20table%20is%20%60%22Order%20Details%22%60%20with%20a%20space%20in%20it%2C%20and%20every%20mention%20of%20it%20needs%20double%0A%20%20%20%20quotes.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(duckdb%2C%20mo)%3A%0A%20%20%20%20%23%20Look%20for%20a%20data%2Fnorthwind%20folder%20here%20and%20in%20every%20folder%20above%2C%20so%20the%0A%20%20%20%20%23%20chapter%20works%20wherever%20you%20save%20it.%20If%20there%20is%20none%2C%20read%20the%20copy%20on%20the%0A%20%20%20%20%23%20course%20site%20instead.%0A%20%20%20%20_here%20%3D%20mo.notebook_dir()%0A%20%20%20%20_source%20%3D%20next(%0A%20%20%20%20%20%20%20%20(p%20%2F%20%22data%22%20%2F%20%22northwind%22%20for%20p%20in%20%5B_here%2C%20*_here.parents%5D%0A%20%20%20%20%20%20%20%20%20if%20(p%20%2F%20%22data%22%20%2F%20%22northwind%22).is_dir())%2C%0A%20%20%20%20%20%20%20%20%22https%3A%2F%2Foim7510.github.io%2Fdata%2Fnorthwind%22%2C%0A%20%20%20%20)%0A%0A%20%20%20%20con%20%3D%20duckdb.connect()%0A%0A%20%20%20%20_files%20%3D%20%7B%0A%20%20%20%20%20%20%20%20%22categories%22%3A%20%22categories%22%2C%0A%20%20%20%20%20%20%20%20%22customercustomerdemo%22%3A%20%22customercustomerdemo%22%2C%0A%20%20%20%20%20%20%20%20%22customerdemographics%22%3A%20%22customerdemographics%22%2C%0A%20%20%20%20%20%20%20%20%22customers%22%3A%20%22customers%22%2C%0A%20%20%20%20%20%20%20%20%22employees%22%3A%20%22employees%22%2C%0A%20%20%20%20%20%20%20%20%22employeeterritories%22%3A%20%22employeeterritories%22%2C%0A%20%20%20%20%20%20%20%20%22Order%20Details%22%3A%20%22order-details%22%2C%0A%20%20%20%20%20%20%20%20%22orders%22%3A%20%22orders%22%2C%0A%20%20%20%20%20%20%20%20%22products%22%3A%20%22products%22%2C%0A%20%20%20%20%20%20%20%20%22regions%22%3A%20%22regions%22%2C%0A%20%20%20%20%20%20%20%20%22shippers%22%3A%20%22shippers%22%2C%0A%20%20%20%20%20%20%20%20%22suppliers%22%3A%20%22suppliers%22%2C%0A%20%20%20%20%20%20%20%20%22territories%22%3A%20%22territories%22%2C%0A%20%20%20%20%7D%0A%20%20%20%20for%20_table%2C%20_file%20in%20_files.items()%3A%0A%20%20%20%20%20%20%20%20con.execute(%0A%20%20%20%20%20%20%20%20%20%20%20%20f%22%22%22CREATE%20TABLE%20%22%7B_table%7D%22%20AS%20SELECT%20*%20FROM%20'%7B_source%7D%2F%7B_file%7D.parquet'%22%22%22%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20return%20(con%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%206.%20%60SELECT%60%20and%20%60FROM%60%0A%0A%20%20%20%20A%20query%20is%20a%20sentence%20with%20a%20fixed%20vocabulary.%20The%20two%20words%20you%20cannot%20leave%20out%20are%0A%20%20%20%20%60SELECT%60%2C%20which%20names%20the%20columns%20you%20want%20back%2C%20and%20%60FROM%60%2C%20which%20names%20the%20table%0A%20%20%20%20they%20come%20from.%20Everything%20else%20in%20this%20chapter%20narrows%2C%20sorts%2C%20groups%20or%20counts%20what%0A%20%20%20%20those%20two%20give%20you.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60SELECT%60%20names%20columns%20and%20%60FROM%60%20names%20the%20table%0A%0A%20%20%20%20%60products%60%20has%2010%20columns%20and%2077%20rows.%20This%20asks%20for%20three%20of%20the%20columns%20and%2C%20for%0A%20%20%20%20now%2C%20five%20of%20the%20rows.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductID%22%2C%20%22ProductName%22%2C%20%22UnitPrice%22%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20LIMIT%205%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20result%20is%20a%20table.%20It%20has%20the%20columns%20you%20asked%20for%2C%20in%20the%20order%20you%20asked%20for%0A%20%20%20%20them%2C%20and%20it%20is%20not%20stored%20anywhere.%20The%20database%20built%20it%20when%20you%20pressed%20run%20and%0A%20%20%20%20will%20build%20it%20again%20next%20time.%0A%0A%20%20%20%20Two%20habits%20are%20worth%20starting%20with.%20Put%20each%20**clause**%2C%20a%20keyword%20and%20what%20follows%0A%20%20%20%20it%2C%20on%20its%20own%20line%2C%20because%20a%20query%20grows%20and%20a%20one-line%20query%20stops%20being%20readable%0A%20%20%20%20at%20about%20the%20third%20clause.%20And%20write%20the%20keywords%20in%20capitals%2C%20which%20nothing%20enforces%0A%20%20%20%20and%20every%20SQL%20reader%20expects.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20ask%20%60customers%60%20for%20%60%22CustomerID%22%60%2C%20%60%22CompanyName%22%60%20and%20%60%22Country%22%60%2C%0A%20%20%20%20five%20rows.%20Then%20swap%20the%20order%20of%20the%20three%20column%20names%20and%20run%20it%20again.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60SELECT%20*%60%20gives%20you%20every%20column%0A%0A%20%20%20%20The%20star%20means%20all%20of%20them%2C%20and%20it%20is%20the%20fastest%20way%20to%20see%20what%20a%20table%20holds.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22SELECT%20*%20FROM%20orders%20LIMIT%202%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Read%20the%20line%20under%20the%20result%3A%20%602%20rows%60%2C%20%6014%20columns%20(7%20shown)%60.%20The%20display%20gave%20up%0A%20%20%20%20and%20hid%20seven%20columns%20behind%20the%20%60%E2%80%A6%60%2C%20which%20is%20what%20a%20wide%20table%20does%20to%20a%20screen.%0A%0A%20%20%20%20**The%20trap.**%20%60SELECT%20*%60%20in%20anything%20you%20keep%20is%20a%20query%20whose%20meaning%20changes%20when%0A%20%20%20%20somebody%20adds%20a%20column%20to%20the%20table.%20It%20is%20a%20good%20way%20to%20look%20and%20a%20poor%20way%20to%0A%20%20%20%20write.%20Name%20the%20columns%20in%20anything%20you%20save%2C%20share%20or%20build%20on.%0A%0A%20%20%20%20%60DESCRIBE%60%20is%20the%20better%20way%20to%20find%20out%20what%20is%20in%20there%2C%20because%20it%20gives%20you%20one%0A%20%20%20%20row%20per%20column%20instead%20of%20one%20column%20per%20column.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20%60DESCRIBE%20orders%60%2C%20then%20write%20a%20%60SELECT%60%20naming%20the%20four%20columns%0A%20%20%20%20you%20would%20actually%20want%20if%20somebody%20asked%20you%20which%20orders%20went%20to%20France.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20name%20with%20a%20space%20in%20it%20needs%20double%20quotes%0A%0A%20%20%20%20DuckDB%20folds%20an%20unquoted%20name%20to%20lower%20case%20and%20matches%20it%20without%20regard%20to%20case%2C%20so%0A%20%20%20%20%60SELECT%20orderid%20FROM%20orders%60%20finds%20the%20column%20called%20%60OrderID%60.%20That%20works%20until%20a%0A%20%20%20%20name%20contains%20something%20the%20parser%20cannot%20read%20as%20a%20single%20word.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22SELECT%20orderid%2C%20shipcountry%20FROM%20orders%20LIMIT%202%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20order-lines%20table%20is%20called%20%60Order%20Details%60%2C%20with%20a%20space.%20Unquoted%2C%20the%20parser%0A%20%20%20%20reads%20%60Order%60%20as%20the%20whole%20name%20and%20then%20finds%20a%20stray%20word%20after%20it%3A%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20SELECT%20*%20FROM%20Order%20Details%20LIMIT%202%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%20Parser%20Error%3A%20syntax%20error%20at%20or%20near%20%22Order%22%0A%0A%20%20%20%20LINE%201%3A%20SELECT%20*%20FROM%20Order%20Details%20LIMIT%202%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5E%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20Double%20quotes%20make%20the%20whole%20thing%20one%20name.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22SELECT%20%22OrderID%22%2C%20%22ProductID%22%2C%20%22Quantity%22%20FROM%20%22Order%20Details%22%20LIMIT%203%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**Double%20quotes%20are%20for%20names.%20Single%20quotes%20are%20for%20text.**%20%60'France'%60%20is%20a%20value%20you%0A%20%20%20%20are%20comparing%20against%20and%20%60%22ShipCountry%22%60%20is%20the%20column%20you%20are%20comparing.%20Swapping%0A%20%20%20%20them%20gives%20you%20either%20a%20column%20that%20does%20not%20exist%20or%20a%20comparison%20against%20the%20literal%0A%20%20%20%20word.%0A%0A%20%20%20%20This%20handbook%20quotes%20every%20column%20name%2C%20which%20is%20more%20punctuation%20than%20DuckDB%20needs.%0A%20%20%20%20Engines%20disagree%20about%20what%20an%20unquoted%20name%20means%2C%20and%20a%20quoted%20one%20means%20the%20same%0A%20%20%20%20thing%20everywhere%2C%20so%20the%20extra%20keystrokes%20are%20what%20makes%20a%20query%20travel.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20%60SELECT%20count(*)%20FROM%20%22Order%20Details%22%60%2C%20then%20delete%20the%20double%0A%20%20%20%20quotes%20and%20read%20the%20message%20you%20get.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20column%20can%20be%20one%20you%20compute%0A%0A%20%20%20%20Nothing%20says%20a%20%60SELECT%60%20item%20has%20to%20be%20a%20column%20that%20exists.%20An%20expression%20is%20a%0A%20%20%20%20column%20too%2C%20and%20it%20is%20computed%20once%20per%20row.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductName%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22UnitsInStock%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22ReorderLevel%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22UnitsInStock%22%20-%20%22ReorderLevel%22%20AS%20above_reorder%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20LIMIT%205%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60AS%20above_reorder%60%20gives%20the%20computed%20column%20a%20name%2C%20an%20**alias**.%20Without%20one%2C%20DuckDB%0A%20%20%20%20names%20it%20after%20the%20expression%2C%20and%20a%20column%20called%20%60(%22UnitsInStock%22%20-%20%22ReorderLevel%22)%60%0A%20%20%20%20is%20awkward%20to%20refer%20to%20and%20worse%20to%20read.%0A%0A%20%20%20%20Two%20rows%20there%20are%20negative%2C%20which%20means%20those%20products%20are%20below%20the%20level%20at%20which%0A%20%20%20%20somebody%20meant%20to%20reorder%20them.%20Nothing%20in%20the%20table%20said%20so%3B%20the%20subtraction%20said%20so.%0A%0A%20%20%20%20An%20alias%20that%20needs%20a%20space%20or%20a%20capital%20gets%20double%20quotes%2C%20for%20the%20same%20reason%20a%0A%20%20%20%20table%20name%20does.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductName%22%20AS%20%22Product%20name%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(%22UnitPrice%22%2C%202)%20AS%20%22Price%22%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20LIMIT%203%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60round(...%2C%202)%60%20is%20doing%20real%20work%20there.%20%60UnitPrice%60%20is%20stored%20as%20%60DECIMAL(12%2C4)%60%20and%0A%20%20%20%20prints%20as%20%6018.0000%60%2C%20so%20a%20price%20quoted%20as%20%6018.00%60%20in%20a%20memo%20came%20from%20somewhere%20and%20it%0A%20%20%20%20is%20worth%20knowing%20where.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20add%20a%20column%20to%20that%20query%20holding%20%60%22UnitPrice%22%20*%20%22UnitsInStock%22%60%2C%0A%20%20%20%20round%20it%20to%20two%20decimals%2C%20and%20name%20it%20%60stock_value%60.%20Then%20state%20in%20one%20sentence%20what%0A%20%20%20%20that%20number%20means%20for%20the%20business.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20You%20run%20%60SELECT%20*%20FROM%20orders%60%20and%20the%20result%20says%20%6014%20columns%20(7%20shown)%60.%20What%0A%20%20%20%20%20%20%20does%20that%20tell%20you%20about%20the%20table%2C%20and%20what%20does%20it%20tell%20you%20about%20the%20query%3F%0A%20%20%20%202.%20A%20colleague's%20saved%20report%20starts%20%60SELECT%20*%20FROM%20products%60.%20Somebody%20adds%20a%20column%0A%20%20%20%20%20%20%20to%20%60products%60%20on%20Monday.%20What%20happens%20to%20the%20report%3F%0A%20%20%20%203.%20When%20does%20a%20column%20name%20need%20double%20quotes%2C%20and%20when%20does%20a%20value%20need%20single%0A%20%20%20%20%20%20%20quotes%3F%0A%20%20%20%204.%20%60%22UnitsInStock%22%20-%20%22ReorderLevel%22%60%20returns%20a%20negative%20number%20for%20two%20of%20the%20five%0A%20%20%20%20%20%20%20rows%20above.%20Is%20that%20a%20data%20problem%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%207.%20%60WHERE%60%0A%0A%20%20%20%20%60SELECT%60%20chooses%20columns%20and%20%60WHERE%60%20chooses%20rows.%20It%20takes%20a%20condition%2C%20called%20a%0A%20%20%20%20**predicate**%2C%20tests%20it%20once%20per%20row%2C%20and%20keeps%20the%20rows%20where%20the%20answer%20is%20true.%0A%20%20%20%20Most%20of%20the%20mistakes%20in%20this%20chapter%20are%20made%20in%20this%20clause%2C%20because%20a%20condition%20that%0A%20%20%20%20is%20wrong%20still%20runs.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20condition%20tested%20on%20every%20row%0A%0A%20%20%20%20Two%20of%20the%2077%20products%20cost%20more%20than%20100.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductID%22%2C%20%22ProductName%22%2C%20round(%22UnitPrice%22%2C%202)%20AS%20price%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20WHERE%20%22UnitPrice%22%20%3E%20100%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20comparison%20operators%20are%20%60%3D%60%2C%20%60%3C%3E%60%2C%20%60%3C%60%2C%20%60%3C%3D%60%2C%20%60%3E%60%20and%20%60%3E%3D%60.%20SQL%20writes%20%22not%20equal%0A%20%20%20%20to%22%20as%20%60%3C%3E%60%3B%20%60!%3D%60%20also%20works%20in%20DuckDB%20and%20in%20most%20engines%2C%20and%20%60%3C%3E%60%20is%20the%20one%20that%0A%20%20%20%20works%20everywhere.%0A%0A%20%20%20%20Note%20what%20the%20query%20did%20not%20need.%20There%20is%20no%20%60WHERE%20%22UnitPrice%22%20IS%20A%20NUMBER%60%2C%20because%0A%20%20%20%20Chapter%20A%20%C2%A71%20already%20established%20that%20the%20column%20has%20one%20type%20and%20every%20row%20obeys%20it.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20find%20the%20products%20with%20fewer%20than%2010%20units%20in%20stock.%20Then%20find%20the%20ones%0A%20%20%20%20with%20none%20at%20all%2C%20and%20state%20whether%20those%20two%20questions%20have%20the%20same%20answer.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Quoting%20text%20and%20doubling%20an%20apostrophe%0A%0A%20%20%20%20Six%20customers%20have%20an%20apostrophe%20in%20their%20name.%20%60Bon%20app'%60%20is%20one%20of%20them%2C%20and%20typing%0A%20%20%20%20it%20into%20a%20query%20is%20the%20first%20place%20the%20difference%20between%20quoting%20a%20name%20and%20quoting%20a%0A%20%20%20%20value%20gets%20sharp.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22CustomerID%22%2C%20%22CompanyName%22%2C%20%22City%22%2C%20%22Country%22%0A%20%20%20%20%20%20%20%20FROM%20customers%0A%20%20%20%20%20%20%20%20WHERE%20%22CompanyName%22%20%3D%20'Bon%20app'''%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20That%20is%20three%20single%20quotes%20at%20the%20end.%20Two%20of%20them%20are%20the%20apostrophe%20in%20the%20name%0A%20%20%20%20and%20the%20third%20closes%20the%20string.%20SQL%20escapes%20a%20single%20quote%20by%20doubling%20it%2C%20and%0A%20%20%20%20nothing%20else.%20A%20backslash%20does%20not%20do%20it.%0A%0A%20%20%20%20**The%20trap.**%20An%20unescaped%20apostrophe%20does%20not%20give%20you%20the%20wrong%20rows.%20It%20ends%20the%0A%20%20%20%20string%20early%20and%20leaves%20the%20rest%20of%20the%20name%20sitting%20in%20the%20query%20as%20syntax%2C%20so%20you%0A%20%20%20%20get%20a%20parser%20error%20whose%20message%20points%20at%20a%20piece%20of%20a%20company%20name.%20When%20that%0A%20%20%20%20happens%2C%20count%20the%20quotes%20before%20you%20read%20anything%20else.%0A%0A%20%20%20%20Nine%20product%20names%20carry%20one%20too%2C%20including%20%60Chef%20Anton's%20Cajun%20Seasoning%60%20and%0A%20%20%20%20%60Sirop%20d'%C3%A9rable%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductID%22%2C%20%22ProductName%22%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20WHERE%20%22ProductName%22%20LIKE%20'%25''%25'%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22ProductID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20write%20the%20%60WHERE%60%20that%20finds%20the%20customer%20called%20%60B's%20Beverages%60%2C%20by%0A%20%20%20%20exact%20match.%20Then%20try%20it%20once%20with%20a%20single%20apostrophe%20and%20read%20the%20error.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Check%20the%20type%20before%20you%20write%20the%20comparison%0A%0A%20%20%20%20%60products.Discontinued%60%20holds%20a%200%20or%20a%201%20and%20looks%20like%20a%20number.%20%60DESCRIBE%60%20says%0A%20%20%20%20otherwise.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22SELECT%20*%20FROM%20(DESCRIBE%20products)%20WHERE%20column_name%20%3D%20'Discontinued'%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20It%20is%20a%20%60VARCHAR%60%2C%20which%20is%20the%20column's%20**data%20type**%2C%20so%20the%20value%20is%20the%20text%0A%20%20%20%20%60'0'%60%2C%20and%20the%20right%20comparison%20is%20%60%22Discontinued%22%20%3D%20'0'%60.%20DuckDB%20is%20forgiving%20here%0A%20%20%20%20and%20will%20**cast**%20for%20you%2C%20converting%20the%20text%20to%20a%20number%2C%20so%20%60%3D%200%60%20also%20returns%2069%0A%20%20%20%20rows.%20Comparing%20a%20text%20column%20to%20a%20number%20is%20the%20kind%20of%20line%20that%20depends%20on%20the%0A%20%20%20%20engine%20being%20generous%2C%20and%20%60DESCRIBE%60%20costs%20one%20query.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22Discontinued%22%2C%20count(*)%20AS%20n_products%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20GROUP%20BY%20%22Discontinued%22%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22Discontinued%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20That%20is%2069%20current%20products%20and%208%20discontinued%20ones.%20This%20chapter%20writes%20%60%3D%20'0'%60%0A%20%20%20%20everywhere.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20%60DESCRIBE%20orders%60%20and%20find%20every%20column%20whose%20type%20would%20surprise%0A%20%20%20%20you%20if%20you%20guessed%20from%20the%20name%20alone.%20%60Freight%60%20and%20the%20three%20date%20columns%20are%20the%0A%20%20%20%20ones%20to%20look%20at.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60AND%60%20binds%20tighter%20than%20%60OR%60%0A%0A%20%20%20%20Two%20conditions%20joined%20with%20%60AND%60%20keep%20the%20rows%20where%20both%20hold.%20Joined%20with%20%60OR%60%2C%20they%0A%20%20%20%20keep%20the%20rows%20where%20either%20one%20holds.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20AS%20france_and_heavy%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22ShipCountry%22%20%3D%20'France'%20AND%20%22Freight%22%20%3E%20100%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Mixing%20the%20two%20is%20where%20it%20goes%20wrong.%20Read%20these%20two%20out%20loud%20and%20they%20sound%0A%20%20%20%20identical.%20They%20differ%20by%20one%20pair%20of%20parentheses.%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20WHERE%20%22ShipCountry%22%20%3D%20'France'%20OR%20%22ShipCountry%22%20%3D%20'Belgium'%20AND%20%22Freight%22%20%3E%20500%0A%20%20%20%20WHERE%20(%22ShipCountry%22%20%3D%20'France'%20OR%20%22ShipCountry%22%20%3D%20'Belgium')%20AND%20%22Freight%22%20%3E%20500%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20The%20query%20below%20runs%20both%20and%20puts%20the%20two%20answers%20in%20one%20result%20with%20%60UNION%20ALL%60%2C%20a%0A%20%20%20%20**set%20operator**%20that%20stacks%20one%20result%20on%20top%20of%20another.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'no%20parentheses'%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22ShipCountry%22%20%3D%20'France'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20OR%20%22ShipCountry%22%20%3D%20'Belgium'%20AND%20%22Freight%22%20%3E%20500)%20AS%20rows_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'with%20parentheses'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20(%22ShipCountry%22%20%3D%20'France'%20OR%20%22ShipCountry%22%20%3D%20'Belgium')%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20%22Freight%22%20%3E%20500)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20first%20line%20returns%201%2C783%20rows%20and%20the%20second%20returns%2056.%0A%0A%20%20%20%20**The%20trap.**%20%60AND%60%20is%20evaluated%20before%20%60OR%60%2C%20the%20way%20%60*%60%20is%20evaluated%20before%20%60%2B%60.%20So%0A%20%20%20%20the%20first%20line%20means%20%22France%2C%20or%20else%20Belgium%20with%20heavy%20freight%22%2C%20and%20it%20hands%20back%0A%20%20%20%20every%20French%20order%20regardless%20of%20freight.%20Both%20queries%20run%2C%20both%20return%20a%20plausible%0A%20%20%20%20count%2C%20and%20only%20one%20of%20them%20answers%20the%20question%20somebody%20asked.%0A%0A%20%20%20%20Put%20parentheses%20around%20every%20%60OR%60%20you%20write%20next%20to%20an%20%60AND%60%2C%20even%20where%20they%20change%0A%20%20%20%20nothing.%20They%20cost%20one%20character%20and%20they%20remove%20the%20reader's%20need%20to%20remember%20a%0A%20%20%20%20precedence%20rule.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20predict%2C%20then%20run%3A%20how%20many%20orders%20went%20to%20France%20or%20Belgium%20with%0A%20%20%20%20freight%20over%20500%3F%20Now%20write%20the%20same%20question%20with%20%60IN%60%20and%20check%20that%20you%20get%2056.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60IN%60%20and%20%60BETWEEN%60%20are%20shorthand%0A%0A%20%20%20%20%60IN%60%20takes%20a%20list%20and%20is%20a%20tidier%20%60OR%60.%20%60BETWEEN%60%20takes%20two%20bounds%20and%20includes%20both%0A%20%20%20%20of%20them.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'IN%20three%20countries'%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22ShipCountry%22%20IN%20('France'%2C%20'Belgium'%2C%20'Spain'))%20AS%20rows_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'BETWEEN%2020%20and%2050%2C%20products'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20products%20WHERE%20%22UnitPrice%22%20BETWEEN%2020%20AND%2050)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60BETWEEN%2020%20AND%2050%60%20includes%20a%20product%20priced%20at%20exactly%2020%20and%20one%20priced%20at%20exactly%0A%20%20%20%2050.%20Both%20ends%20are%20in.%20That%20is%20fine%20for%20a%20price%20and%20it%20is%20a%20real%20problem%20for%20a%20date%2C%0A%20%20%20%20which%20is%20%C2%A712.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20write%20the%20same%2031-product%20filter%20without%20%60BETWEEN%60%2C%20using%20two%0A%20%20%20%20conditions%20and%20an%20%60AND%60.%20Confirm%20the%20count%20matches.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Matching%20a%20pattern%20with%20%60LIKE%60%0A%0A%20%20%20%20%60%25%60%20stands%20for%20any%20run%20of%20characters%2C%20including%20none.%20%60_%60%20stands%20for%20exactly%20one.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'LIKE%20''Ch%25'''%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20products%20WHERE%20%22ProductName%22%20LIKE%20'Ch%25')%20AS%20rows_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'LIKE%20''ch%25'''%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20products%20WHERE%20%22ProductName%22%20LIKE%20'ch%25')%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'ILIKE%20''ch%25'''%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20products%20WHERE%20%22ProductName%22%20ILIKE%20'ch%25')%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**The%20trap.**%20The%20middle%20line%20returns%20zero%2C%20and%20nothing%20on%20the%20screen%20says%20the%0A%20%20%20%20pattern%20was%20one%20keystroke%20away%20from%20six%20rows.%20A%20%60LIKE%60%20that%20comes%20back%20empty%20is%20a%20case%0A%20%20%20%20question%20before%20it%20is%20a%20data%20question.%0A%0A%20%20%20%20%60ILIKE%60%20is%20the%20case-insensitive%20form.%20Not%20every%20engine%20has%20it%2C%20and%0A%20%20%20%20%60lower(%22ProductName%22)%20LIKE%20'ch%25'%60%20does%20the%20same%20job%20everywhere.%20Case%20sensitivity%20here%0A%20%20%20%20is%20a%20property%20of%20the%20column%20called%20its%20**collation**%2C%20and%20engines%20ship%20different%0A%20%20%20%20defaults%20for%20it.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20find%20every%20product%20whose%20name%20contains%20the%20word%20%60Chef%60%2C%20then%20every%20one%0A%20%20%20%20that%20ends%20in%20%60Mix%60%2C%20then%20every%20one%20whose%20name%20is%20exactly%20five%20characters%20long.%20The%0A%20%20%20%20last%20one%20wants%20%60_%60%20five%20times%2C%20or%20%60length()%60.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20A%20filter%20returns%201%2C783%20rows%20and%20your%20colleague%20expected%20about%2050.%20Where%20do%20you%20look%0A%20%20%20%20%20%20%20first%3F%0A%20%20%20%202.%20%60WHERE%20%22ProductName%22%20LIKE%20'ch%25'%60%20returns%20nothing.%20Name%20two%20different%20explanations%0A%20%20%20%20%20%20%20and%20explain%20how%20you%20would%20tell%20them%20apart.%0A%20%20%20%203.%20%60BETWEEN%2020%20AND%2050%60%20on%20a%20price%20column.%20Which%20products%20are%20on%20the%20boundary%2C%20and%20are%0A%20%20%20%20%20%20%20they%20in%20or%20out%3F%0A%20%20%20%204.%20%60products.Discontinued%60%20is%20a%20%60VARCHAR%60%20and%20%60%22Discontinued%22%20%3D%200%60%20still%20returns%2069%0A%20%20%20%20%20%20%20rows%20in%20DuckDB.%20Why%20is%20that%20worth%20knowing%20about%20before%20you%20move%20the%20query%20to%0A%20%20%20%20%20%20%20another%20engine%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%208.%20%60ORDER%20BY%60%20and%20%60LIMIT%60%0A%0A%20%20%20%20Chapter%20A%20%C2%A71%20said%20the%20order%20of%20the%20rows%20is%20not%20part%20of%20the%20table.%20%60ORDER%20BY%60%20is%20how%0A%20%20%20%20you%20ask%20for%20one.%20%60LIMIT%60%20cuts%20the%20result%20short%20after%20the%20sorting%20is%20done%2C%20and%20the%20two%0A%20%20%20%20of%20them%20almost%20always%20travel%20together.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60ORDER%20BY%60%20and%20%60DESC%60%0A%0A%20%20%20%20Ascending%20is%20the%20default%2C%20so%20%60ORDER%20BY%20%22UnitPrice%22%60%20starts%20at%20the%20cheapest.%20%60DESC%60%0A%20%20%20%20starts%20at%20the%20most%20expensive.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductName%22%2C%20round(%22UnitPrice%22%2C%202)%20AS%20price%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22UnitPrice%22%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%205%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60C%C3%B4te%20de%20Blaye%60%20at%20263.50%20is%20more%20than%20twice%20the%20price%20of%20the%20next%20product%20down%2C%20which%0A%20%20%20%20is%20the%20sort%20of%20thing%20you%20find%20in%20the%20first%20minute%20of%20looking%20and%20not%20in%20a%20summary.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20same%20query%20without%20%60DESC%60%20and%20read%20the%20first%20five.%20Then%20state%0A%20%20%20%20which%20of%20the%20two%20lists%20you%20would%20put%20in%20front%20of%20a%20buyer.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20second%20sort%20key%20breaks%20the%20ties%0A%0A%20%20%20%20Sort%20by%20country%20and%20the%20customers%20inside%20each%20country%20come%20back%20in%20whatever%20order%20the%0A%20%20%20%20engine%20finds%20convenient.%20A%20second%20column%20decides%20that%20too.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22Country%22%2C%20%22City%22%2C%20%22CompanyName%22%0A%20%20%20%20%20%20%20%20FROM%20customers%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22Country%22%2C%20%22CompanyName%22%0A%20%20%20%20%20%20%20%20LIMIT%208%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20three%20Argentinian%20customers%20are%20alphabetical%20by%20company%20because%20of%20the%20second%20key.%0A%20%20%20%20Each%20key%20can%20take%20its%20own%20direction%3A%20%60ORDER%20BY%20%22Country%22%2C%20%22CompanyName%22%20DESC%60%20sorts%0A%20%20%20%20countries%20up%20and%20companies%20down%20inside%20each%20one.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20sort%20%60orders%60%20by%20%60%22ShipCountry%22%60%20and%20then%20by%20%60%22Freight%22%60%20descending%2C%0A%20%20%20%20and%20take%20the%20first%2010.%20Which%20country%20did%20you%20get%2C%20and%20is%20that%20a%20fact%20about%20freight%20or%0A%20%20%20%20a%20fact%20about%20the%20alphabet%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20You%20can%20sort%20by%20a%20name%20you%20invented%20in%20%60SELECT%60%0A%0A%20%20%20%20%60doubled%60%20does%20not%20exist%20in%20%60products%60.%20It%20exists%20because%20this%20query%20made%20it%2C%20and%0A%20%20%20%20%60ORDER%20BY%60%20can%20still%20use%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductName%22%2C%20round(%22UnitPrice%22%20*%202%2C%202)%20AS%20doubled%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20ORDER%20BY%20doubled%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%205%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20That%20works%20because%20%60SELECT%60%20has%20already%20run%20by%20the%20time%20%60ORDER%20BY%60%20is%20applied%2C%20which%0A%20%20%20%20is%20%C2%A716%20and%20is%20worth%20coming%20back%20to.%20For%20now%20it%20is%20a%20convenience%3A%20name%20the%20expression%0A%20%20%20%20once%20and%20sort%20by%20the%20name.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20compute%20%60%22UnitsInStock%22%20*%20%22UnitPrice%22%20AS%20stock_value%60%2C%20sort%20by%20it%0A%20%20%20%20descending%2C%20and%20take%20the%20top%205.%20Then%20work%20out%20what%20share%20of%20the%20whole%20inventory's%20value%0A%20%20%20%20those%20five%20hold%2C%20with%20%60SELECT%20round(sum(%22UnitsInStock%22%20*%20%22UnitPrice%22)%2C%202)%20FROM%20products%60%0A%20%20%20%20for%20the%20whole.%20*Check%20yourself%3A%20about%20a%20quarter%2C%2025.1%25.*%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60LIMIT%60%20without%20%60ORDER%20BY%60%20means%20nothing%20in%20particular%0A%0A%20%20%20%20%60LIMIT%203%60%20returns%20three%20rows.%20Which%20three%20is%20up%20to%20the%20engine.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22SELECT%20%22ProductName%22%2C%20round(%22UnitPrice%22%2C%202)%20AS%20price%20FROM%20products%20LIMIT%203%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Those%20happen%20to%20be%20the%20first%20three%20rows%20in%20the%20parquet%20file.%20Nothing%20promised%20that%2C%0A%20%20%20%20and%20a%20database%20that%20stored%20the%20same%20table%20differently%20is%20entitled%20to%20hand%20you%20three%0A%20%20%20%20others.%0A%0A%20%20%20%20**The%20trap.**%20%22The%20top%203%20products%22%20written%20as%20%60LIMIT%203%60%20with%20no%20%60ORDER%20BY%60%20is%20a%20claim%0A%20%20%20%20about%20the%20business%20made%20from%20an%20accident%20of%20storage.%20It%20produces%20three%20rows%2C%20they%20look%0A%20%20%20%20like%20an%20answer%2C%20and%20there%20is%20nothing%20on%20the%20screen%20to%20say%20they%20are%20arbitrary.%20Any%0A%20%20%20%20%60LIMIT%60%20you%20show%20somebody%20needs%20an%20%60ORDER%20BY%60%20above%20it.%0A%0A%20%20%20%20%60OFFSET%60%20skips%20before%20it%20takes%2C%20which%20is%20how%20you%20get%20the%20second%20page.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductName%22%2C%20round(%22UnitPrice%22%2C%202)%20AS%20price%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22UnitPrice%22%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%205%20OFFSET%205%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Products%20six%20to%2010%20by%20price.%20%60OFFSET%60%20without%20%60ORDER%20BY%60%20has%20the%20same%20problem%20as%0A%20%20%20%20%60LIMIT%60%20without%20one%2C%20and%20worse%2C%20because%20two%20pages%20of%20an%20unsorted%20result%20can%20repeat%20a%0A%20%20%20%20row%20and%20miss%20another.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20get%20products%2011%20to%2015%20by%20price.%20Then%20run%20the%20same%20query%20twice%20and%0A%20%20%20%20confirm%20you%20get%20the%20same%20five%20rows%20both%20times.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Where%20the%20missing%20values%20sort%0A%0A%20%20%20%20%60employees.ReportsTo%60%20has%20one%20NULL%20in%20nine%20rows.%20Sorting%20on%20that%20column%20has%20to%20put%20it%0A%20%20%20%20somewhere.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22EmployeeID%22%2C%20%22LastName%22%2C%20%22ReportsTo%22%0A%20%20%20%20%20%20%20%20FROM%20employees%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22ReportsTo%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20DuckDB%20puts%20NULLs%20last%20in%20an%20ascending%20sort%20and%20last%20again%20in%20a%20descending%20one%2C%20so%20the%0A%20%20%20%20row%20you%20cannot%20rank%20stays%20out%20of%20the%20way%20at%20the%20bottom%20either%20way.%20That%20is%20a%20choice%20the%0A%20%20%20%20engine%20made%2C%20and%20engines%20make%20it%20differently%2C%20so%20a%20query%20moved%20somewhere%20else%20can%20put%0A%20%20%20%20the%20same%20row%20at%20the%20top.%0A%0A%20%20%20%20%60NULLS%20FIRST%60%20and%20%60NULLS%20LAST%60%20say%20it%20out%20loud%20and%20travel%20between%20engines.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22EmployeeID%22%2C%20%22LastName%22%2C%20%22ReportsTo%22%0A%20%20%20%20%20%20%20%20FROM%20employees%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22ReportsTo%22%20NULLS%20FIRST%0A%20%20%20%20%20%20%20%20LIMIT%203%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**The%20trap.**%20%60ORDER%20BY%20%22ReportsTo%22%20DESC%20LIMIT%201%60%20to%20find%20the%20highest%20manager%20id%20gives%0A%20%20%20%20you%20employee%206%2C%20and%20the%20one%20person%20who%20reports%20to%20nobody%20is%20the%20row%20you%20were%20actually%0A%20%20%20%20looking%20for.%20%C2%A79%20is%20what%20a%20missing%20value%20does%20to%20everything%20else.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20sort%20%60orders%60%20by%20%60%22ShippedDate%22%60%20descending%20and%20take%20the%20first%20five.%0A%20%20%20%20Then%20add%20%60NULLS%20FIRST%60%20and%20take%20the%20first%20five%20again.%20State%20what%20each%20of%20the%20two%20lists%0A%20%20%20%20is%20a%20list%20of.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20A%20report%20shows%20%22our%20five%20biggest%20orders%22%20and%20the%20query%20ends%20%60LIMIT%205%60%20with%20no%0A%20%20%20%20%20%20%20%60ORDER%20BY%60.%20What%20is%20wrong%20with%20the%20report%2C%20and%20would%20you%20be%20able%20to%20see%20it%20in%20the%0A%20%20%20%20%20%20%20output%3F%0A%20%20%20%202.%20You%20sort%20by%20%60%22Country%22%60%20alone%20and%20run%20the%20query%20on%20two%20different%20machines.%20Can%20the%0A%20%20%20%20%20%20%20rows%20inside%20%60Germany%60%20come%20back%20in%20a%20different%20order%3F%0A%20%20%20%203.%20%60ORDER%20BY%20%22ShippedDate%22%20DESC%20LIMIT%201%60%20on%20%60orders%60.%20What%20do%20you%20get%2C%20and%20what%20did%0A%20%20%20%20%20%20%20you%20probably%20want%3F%0A%20%20%20%204.%20What%20does%20%60LIMIT%205%20OFFSET%205%60%20give%20you%2C%20and%20what%20has%20to%20be%20true%20for%20the%20answer%20to%0A%20%20%20%20%20%20%20be%20stable%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%209.%20%60NULL%60%2C%20and%20why%20it%20is%20not%20zero%0A%0A%20%20%20%20%60NULL%60%20is%20what%20a%20database%20puts%20in%20a%20cell%20when%20it%20has%20no%20value%20for%20it.%20It%20is%20not%20zero%2C%0A%20%20%20%20it%20is%20not%20an%20empty%20string%2C%20and%20it%20is%20not%20%60FALSE%60.%20It%20means%20the%20value%20is%20unknown%2C%20and%0A%20%20%20%20SQL%20takes%20that%20literally%2C%20so%20anything%20you%20ask%20about%20an%20unknown%20value%20comes%20back%0A%20%20%20%20unknown%2C%20which%20is%20the%20third%20answer%20alongside%20true%20and%20false%20in%20SQL's%0A%20%20%20%20**three-valued%20logic**.%0A%0A%20%20%20%20Every%20trap%20in%20this%20section%20follows%20from%20that%20one%20sentence.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20missing%20value%20in%20%60employees%60%0A%0A%20%20%20%20%60employees%60%20is%20small%20enough%20to%20read%20whole.%20Andrew%20Fuller%20is%20the%20Vice%20President%20and%20he%0A%20%20%20%20reports%20to%20nobody%2C%20so%20his%20%60ReportsTo%60%20is%20%60NULL%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22EmployeeID%22%2C%20%22FirstName%22%2C%20%22LastName%22%2C%20%22Title%22%2C%20%22ReportsTo%22%0A%20%20%20%20%20%20%20%20FROM%20employees%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22EmployeeID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20A%20zero%20in%20that%20column%20would%20be%20a%20claim%20that%20he%20reports%20to%20employee%200%2C%20and%20no%20such%0A%20%20%20%20person%20exists.%20The%20%60NULL%60%20says%20the%20database%20has%20nothing%20to%20put%20there%2C%20and%20the%20reason%0A%20%20%20%20here%20is%20structural%2C%20because%20somebody%20has%20to%20be%20at%20the%20top.%0A%0A%20%20%20%20Three%20different%20situations%20all%20produce%20a%20%60NULL%60%20and%20only%20the%20third%20is%20a%20data%20quality%0A%20%20%20%20problem.%0A%0A%20%20%20%20%7C%20%7C%20example%20in%20this%20warehouse%20%7C%20what%20it%20means%20%7C%0A%20%20%20%20%7C---%7C---%7C---%7C%0A%20%20%20%20%7C%20it%20cannot%20apply%20%7C%20%60employees.ReportsTo%60%2C%201%20of%209%20%7C%20the%20VP%20has%20no%20manager%20%7C%0A%20%20%20%20%7C%20it%20has%20not%20happened%20yet%20%7C%20%60orders.ShippedDate%60%2C%2021%20of%2016%2C282%20%7C%20placed%20and%20never%20shipped%20%7C%0A%20%20%20%20%7C%20nobody%20recorded%20it%20%7C%20%60customers.Region%60%2C%202%20of%2093%20%7C%20the%20address%20has%20a%20region%20and%20the%20row%20does%20not%20%7C%0A%0A%20%20%20%20%23%23%23%20In%20Excel%20terms%0A%0A%20%20%20%20A%20blank%20cell%20in%20Excel%20behaves%20as%20zero%20the%20moment%20you%20compare%20it%20to%20something%2C%20so%0A%20%20%20%20%60%3DIF(A1%3E10%2C...)%60%20on%20a%20blank%20cell%20is%20false%20and%20%60%3DA1%2B100%60%20is%20100.%20SQL%20refuses%20both.%20That%0A%20%20%20%20difference%20is%20the%20reason%20a%20spreadsheet%20total%20and%20a%20database%20total%20disagree%20on%20data%0A%20%20%20%20with%20holes%20in%20it%2C%20and%20the%20spreadsheet%20is%20the%20one%20that%20answered%20without%20being%20asked.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20count%20the%20NULLs%20in%20each%20of%20the%20three%20columns%20above%20using%0A%20%20%20%20%60count(*)%20-%20count(%3Ccolumn%3E)%60.%20Predict%20all%20three%20numbers%20first.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Why%20%60%3D%20NULL%60%20finds%20nothing%0A%0A%20%20%20%20Asking%20whether%20an%20unknown%20value%20equals%20%60NULL%60%20asks%20whether%20one%20unknown%20thing%20is%20the%0A%20%20%20%20same%20as%20another.%20SQL%20answers%20%22unknown%22%2C%20%60WHERE%60%20keeps%20only%20rows%20that%20answered%20true%2C%0A%20%20%20%20and%20the%20row%20is%20dropped.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'WHERE%20%22ReportsTo%22%20%3D%20NULL'%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%20WHERE%20%22ReportsTo%22%20%3D%20NULL)%20AS%20rows_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'WHERE%20%22ReportsTo%22%20IS%20NULL'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%20WHERE%20%22ReportsTo%22%20IS%20NULL)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**The%20trap.**%20The%20first%20query%20is%20not%20an%20error.%20It%20runs%2C%20it%20returns%20zero%20rows%2C%20and%20zero%0A%20%20%20%20rows%20reads%20exactly%20like%20%22there%20are%20none%22%2C%20which%20is%20the%20answer%20you%20would%20have%20got%20from%0A%20%20%20%20a%20table%20with%20no%20missing%20values%20at%20all.%20The%20operators%20are%20%60IS%20NULL%60%20and%20%60IS%20NOT%20NULL%60%2C%0A%20%20%20%20and%20they%20are%20the%20only%20ones%20that%20work.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20find%20the%20orders%20that%20have%20never%20shipped%2C%20once%20with%20%60%3D%20NULL%60%20and%20once%0A%20%20%20%20with%20%60IS%20NULL%60.%20One%20of%20them%20gives%20you%2021%20rows.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20%60NULL%60%20spreads%20through%20anything%20it%20touches%0A%0A%20%20%20%20Arithmetic%20on%20an%20unknown%20value%20is%20unknown.%20So%20is%20a%20comparison.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20100%20%2B%20NULL%20%20%20%20%20%20%20AS%20hundred_plus_null%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20NULL%20%3D%20NULL%20%20%20%20%20%20AS%20null_equals_null%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20NULL%20%3C%3E%20NULL%20%20%20%20%20AS%20null_not_equals_null%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20coalesce(NULL%2C%200)%20AS%20coalesced%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60100%20%2B%20NULL%60%20is%20%60NULL%60%2C%20because%20if%20you%20do%20not%20know%20one%20of%20the%20numbers%20you%20do%20not%20know%0A%20%20%20%20the%20total.%20%60NULL%20%3D%20NULL%60%20evaluates%20to%20%60NULL%60%2C%20and%20so%20does%20%60NULL%20%3C%3E%20NULL%60.%20A%20missing%0A%20%20%20%20value%20is%20neither%20equal%20nor%20unequal%20to%20anything.%0A%0A%20%20%20%20%60coalesce(a%2C%20b)%60%20returns%20the%20first%20of%20its%20arguments%20that%20is%20not%20%60NULL%60%2C%20so%0A%20%20%20%20%60coalesce(%22ReportsTo%22%2C%200)%60%20turns%20the%20VP's%20missing%20manager%20into%20a%20zero.%0A%0A%20%20%20%20**The%20trap.**%20That%20is%20a%20decision%2C%20and%20it%20is%20invisible%20three%20steps%20later.%20Once%20the%0A%20%20%20%20%60NULL%60%20is%20a%20zero%2C%20every%20count%20includes%20it%2C%20every%20average%20is%20pulled%20toward%20it%2C%20and%0A%20%20%20%20nothing%20on%20the%20screen%20says%20a%20substitution%20happened.%20Use%20%60coalesce%60%20where%20zero%20is%0A%20%20%20%20genuinely%20the%20right%20value%2C%20such%20as%20a%20quantity%20nobody%20ordered.%20Do%20not%20use%20it%20to%20make%20a%0A%20%20%20%20report%20stop%20showing%20blanks.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20%60SELECT%20%22EmployeeID%22%2C%20%22ReportsTo%22%2C%20coalesce(%22ReportsTo%22%2C%200)%20AS%0A%20%20%20%20filled%20FROM%20employees%20ORDER%20BY%20%22EmployeeID%22%60%2C%20then%20take%20the%20average%20of%20both%20columns%0A%20%20%20%20and%20state%20which%20of%20the%20two%20numbers%20you%20would%20put%20in%20a%20memo.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20%60NULL%60%20falls%20out%20of%20both%20sides%20of%20a%20filter%0A%0A%20%20%20%20Eight%20employees%20report%20to%20somebody.%20Five%20report%20to%20employee%202%20and%20three%20report%20to%0A%20%20%20%20employee%205%2C%20so%20%60%3C%3E%202%60%20should%20find%20three%20people%2C%20plus%20the%20VP%20who%20reports%20to%20nobody.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'WHERE%20%22ReportsTo%22%20%3D%202'%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%20WHERE%20%22ReportsTo%22%20%3D%202)%20AS%20rows_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'WHERE%20%22ReportsTo%22%20%3C%3E%202'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%20WHERE%20%22ReportsTo%22%20%3C%3E%202)%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'WHERE%20%22ReportsTo%22%20%3C%3E%202%20OR%20%22ReportsTo%22%20IS%20NULL'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22ReportsTo%22%20%3C%3E%202%20OR%20%22ReportsTo%22%20IS%20NULL)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Five%20and%20three%20make%20eight%2C%20and%20the%20table%20has%20nine%20rows.%20The%20VP%20is%20in%20neither%20result.%0A%20%20%20%20He%20fails%20%60%3D%202%60%20because%20his%20value%20is%20unknown%2C%20and%20he%20fails%20%60%3C%3E%202%60%20for%20the%20same%20reason.%0A%0A%20%20%20%20**The%20trap.**%20Split%20a%20table%20on%20a%20condition%20and%20its%20opposite%2C%20and%20the%20two%20halves%20do%20not%0A%20%20%20%20add%20up%20to%20the%20whole.%20On%20nine%20rows%20you%20notice.%20On%2016%2C282%20you%20do%20not%2C%20and%20the%20missing%0A%20%20%20%20rows%20are%20silently%20the%20ones%20with%20the%20most%20interesting%20story%2C%20because%20a%20value%20goes%0A%20%20%20%20missing%20for%20a%20reason.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20count%20the%20orders%20shipped%20later%20than%20required%2C%20then%20the%20ones%20shipped%20on%0A%20%20%20%20time%20or%20earlier%2C%20and%20add%20the%20two.%20Compare%20to%2016%2C282%20and%20account%20for%20the%20difference.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20orders%20that%20never%20shipped%0A%0A%20%20%20%20This%20is%20the%20one%20in%20the%20warehouse%20worth%20knowing%20about.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20all_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(%22ShippedDate%22)%20%20%20%20%20%20%20%20%20%20%20%20AS%20have_a_ship_date%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20-%20count(%22ShippedDate%22)%20AS%20never_shipped%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60count(*)%60%20counts%20rows%20and%20%60count(%22ShippedDate%22)%60%20counts%20rows%20where%20that%20column%20has%20a%0A%20%20%20%20value%2C%20so%20the%20gap%20between%20them%20is%20the%20count%20of%20NULLs.%20%C2%A713%20comes%20back%20to%20that%20pair.%0A%0A%20%20%20%20There%20are%2021%20orders%20that%20were%20placed%20and%20have%20no%20ship%20date.%20Treating%20that%20as%20a%0A%20%20%20%20shipping%20time%20of%20zero%20days%20would%20make%20the%20delivery%20numbers%20look%20better%3B%20treating%20it%20as%0A%20%20%20%20a%20very%20long%20delivery%20time%20would%20make%20them%20look%20worse.%20Both%20are%20decisions%2C%20and%20the%20honest%20report%0A%20%20%20%20names%20them%20and%20says%20how%20many%20rows%20are%20involved.%0A%0A%20%20%20%20A%20word%20about%20the%20column%20that%20is%20not%20here.%20%60orders.ShipRegion%60%20is%20the%20famous%20NULL%0A%20%20%20%20example%20in%20every%20Northwind%20tutorial%20written%20before%20this%20one.%20In%20this%20build%20somebody%0A%20%20%20%20filled%20it%20in%2C%20and%20it%20has%20zero%20NULLs%2C%20so%20an%20inherited%20example%20runs%20perfectly%20and%0A%20%20%20%20teaches%20nothing.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20-%20count(%22ShipRegion%22)%20AS%20shipregion_nulls%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20-%20count(%22ShippedDate%22)%20AS%20shippeddate_nulls%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20%60count(*)%20-%20count(%3Ccolumn%3E)%60%20over%20five%20columns%20of%20%60orders%60%20and%20find%0A%20%20%20%20every%20one%20with%20a%20NULL%20in%20it.%20State%2C%20for%20each%2C%20which%20of%20the%20three%20situations%20in%20the%20table%0A%20%20%20%20above%20it%20is.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20A%20column%20is%20blank%20in%20Excel%20and%20%60NULL%60%20in%20the%20database.%20Give%20one%20calculation%20that%0A%20%20%20%20%20%20%20returns%20a%20different%20answer%20in%20each.%0A%20%20%20%202.%20%60WHERE%20%22ShippedDate%22%20%3D%20NULL%60%20returns%20zero%20rows%20and%20%60WHERE%20%22ShippedDate%22%20IS%20NULL%60%0A%20%20%20%20%20%20%20returns%2021.%20Which%20one%20is%20broken%2C%20and%20how%20would%20somebody%20reading%20the%20report%20know%3F%0A%20%20%20%203.%20You%20filter%20on%20%60%22Country%22%20%3C%3E%20'USA'%60%20and%20get%20fewer%20rows%20than%20you%20expected.%20What%20is%0A%20%20%20%20%20%20%20the%20first%20thing%20to%20check%3F%0A%20%20%20%204.%20Somebody%20wraps%20a%20column%20in%20%60coalesce(col%2C%200)%60%20so%20the%20report%20has%20no%20blanks.%20Name%0A%20%20%20%20%20%20%20two%20numbers%20downstream%20that%20just%20changed.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2010.%20%60DISTINCT%60%2C%20and%20what%20it%20hides%0A%0A%20%20%20%20%60DISTINCT%60%20removes%20duplicate%20rows%20from%20a%20result.%20It%20is%20the%20shortest%20fix%20for%20a%20list%0A%20%20%20%20that%20repeats%20itself%2C%20it%20applies%20to%20the%20whole%20row%20and%20not%20to%20one%20column%20of%20it%2C%20and%20it%20is%0A%20%20%20%20worth%20being%20suspicious%20of%20every%20time%20you%20reach%20for%20it.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20One%20row%20per%20different%20value%0A%0A%20%20%20%20%60customers%60%20has%2093%20rows.%20Asking%20for%20the%20country%20column%20gives%2093%20rows%2C%20most%20of%20them%0A%20%20%20%20repeats.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20AS%20customer_rows%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(SELECT%20DISTINCT%20%22Country%22%20FROM%20customers))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20distinct_countries%0A%20%20%20%20%20%20%20%20FROM%20customers%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20So%20the%20query%20returns%2022%20rows%20for%2093%20customers.%20%60DISTINCT%60%20goes%20immediately%20after%0A%20%20%20%20%60SELECT%60%20and%20applies%20to%20everything%20after%20it.%0A%0A%20%20%20%20The%20row%20count%20is%20not%20the%20country%20count.%20Two%20customers%20have%20no%20%60Country%60%2C%20and%20%C2%A79%0A%20%20%20%20already%20said%20a%20%60NULL%60%20is%20a%20value%20the%20row%20carries%2C%20so%0A%20%20%20%20%60DISTINCT%60%20keeps%20one%20%60NULL%60%20row%20alongside%20the%2021%20real%20names.%20Run%0A%20%20%20%20%60count(DISTINCT%20%22Country%22)%60%20on%20the%20same%20column%20and%20it%20answers%2021%2C%20because%20an%0A%20%20%20%20aggregate%20skips%20%60NULL%60%20and%20%60DISTINCT%60%20does%20not.%20**The%20memo%20sentence%20says%2021.**%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22SELECT%20DISTINCT%20%22Country%22%20FROM%20customers%20ORDER%20BY%20%22Country%22%20LIMIT%208%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20list%20the%20distinct%20%60%22ShipCountry%22%60%20values%20in%20%60orders%60%20and%20count%20them.%0A%20%20%20%20Then%20run%20%60count(DISTINCT%20%22ShipCountry%22)%60%20beside%20it.%20The%20two%20agree%20here%20and%20they%0A%20%20%20%20disagreed%20on%20%60customers%60.%20Find%20the%20column%20difference%20that%20explains%20why%2C%20without%0A%20%20%20%20reading%20it%20above.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Adding%20a%20column%20un-deduplicates%20the%20result%0A%0A%20%20%20%20%60DISTINCT%60%20applies%20to%20the%20whole%20row%2C%20so%20two%20rows%20have%20to%20match%20in%20every%20selected%0A%20%20%20%20column%20before%20one%20of%20them%20is%20dropped.%20Adding%20a%20column%20can%20only%20ever%20increase%20the%0A%20%20%20%20number%20of%20rows%20you%20get%20back.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'SELECT%20DISTINCT%20%22Country%22'%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(SELECT%20DISTINCT%20%22Country%22%20FROM%20customers))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20rows_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'SELECT%20DISTINCT%20%22Country%22%2C%20%22City%22'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(SELECT%20DISTINCT%20%22Country%22%2C%20%22City%22%20FROM%20customers))%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20count%20goes%20from%2022%20to%2070.%20Both%20results%20are%20correct%20and%20they%20answer%20different%0A%20%20%20%20questions.%20The%20first%20is%20a%20list%20of%20countries%20and%20the%20second%20is%20a%20list%20of%20places.%0A%0A%20%20%20%20**The%20trap.**%20%60SELECT%20DISTINCT%60%20on%20a%20query%20with%2010%20columns%20in%20it%20removes%20almost%0A%20%20%20%20nothing%2C%20because%2010%20columns%20have%20to%20agree%20before%20two%20rows%20count%20as%20the%20same.%20A%0A%20%20%20%20%60DISTINCT%60%20that%20changed%20the%20row%20count%20by%20three%20on%20a%20result%20of%204%2C000%20has%20not%20cleaned%0A%20%20%20%20anything%20up%3B%20it%20has%20hidden%20three%20rows%20you%20have%20not%20looked%20at.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20%60SELECT%20DISTINCT%20*%20FROM%20orders%60%20and%20compare%20the%20count%20to%2016%2C282.%0A%20%20%20%20Then%20explain%20what%20a%20difference%20of%20zero%20tells%20you%2C%20and%20what%20a%20difference%20of%2040%20would%20have%0A%20%20%20%20told%20you.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60count(DISTINCT%20x)%60%20against%20%60count(x)%60%0A%0A%20%20%20%20The%20two%20counts%20answer%20different%20questions%20and%20the%20difference%20is%20the%20point.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20lines%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(DISTINCT%20%22OrderID%22)%20%20%20%20AS%20orders_covered%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(DISTINCT%20%22ProductID%22)%20%20AS%20products_covered%0A%20%20%20%20%20%20%20%20FROM%20%22Order%20Details%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20There%20are%20609%2C283%20order%20lines%2C%20belonging%20to%2016%2C282%20orders%20and%20mentioning%2077%20different%0A%20%20%20%20products.%20That%20third%20number%20is%20every%20product%20in%20the%20catalog%2C%20so%20every%20one%20of%20them%20appears%20on%20at%0A%20%20%20%20least%20one%20line.%0A%0A%20%20%20%20An%20order%20line%20count%20and%20an%20order%20count%20are%20both%20true%20and%20only%20one%20of%20them%20answers%0A%20%20%20%20%22how%20many%20orders%20did%20we%20take%22.%20Chapter%20C%20%C2%A719%20is%20where%20confusing%20the%20two%20starts%20costing%0A%20%20%20%20real%20money%2C%20because%20a%20join%20can%20turn%20one%20order%20into%20six.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20on%20%60orders%60%2C%20get%20%60count(*)%60%2C%20%60count(DISTINCT%20%22CustomerID%22)%60%20and%0A%20%20%20%20%60count(DISTINCT%20%22ShipCountry%22)%60%20in%20one%20query.%20Write%20the%20three%20numbers%20as%20one%20English%0A%20%20%20%20sentence%20about%20the%20business.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60DISTINCT%60%20and%20%60count(DISTINCT%20...)%60%20disagree%20about%20%60NULL%60%0A%0A%20%20%20%20%60customers.Region%60%20has%20two%20NULLs.%20%60SELECT%20DISTINCT%60%20treats%20them%20as%20one%20more%20value%20to%0A%20%20%20%20list%2C%20and%20%60count(DISTINCT%20...)%60%20ignores%20them%20the%20way%20every%20aggregate%20does.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22SELECT%20DISTINCT%20%22Region%22%20FROM%20customers%20ORDER%20BY%20%22Region%22%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20customer_rows%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(%22Region%22)%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20region_not_null%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(DISTINCT%20%22Region%22)%20%20%20%20AS%20distinct_regions%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(SELECT%20DISTINCT%20%22Region%22%20FROM%20customers))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20distinct_rows%0A%20%20%20%20%20%20%20%20FROM%20customers%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60SELECT%20DISTINCT%20%22Region%22%60%20returns%2010%20rows%20and%20the%20last%20one%20is%20%60NULL%60.%0A%20%20%20%20%60count(DISTINCT%20%22Region%22)%60%20returns%209.%20Same%20column%2C%20same%20query%2C%20one%20row%20of%20difference%2C%0A%20%20%20%20and%20no%20message%20about%20it.%0A%0A%20%20%20%20**The%20trap.**%20%22We%20operate%20in%20nine%20regions%22%20and%20%22the%20region%20column%20takes%2010%20different%0A%20%20%20%20forms%22%20are%20both%20defensible%2C%20and%20a%20slide%20carrying%20one%20of%20them%20was%20produced%20by%20a%20query%0A%20%20%20%20that%20made%20the%20choice%20for%20you.%20%C2%A713%20says%20why%20every%20aggregate%20behaves%20this%20way.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20on%20%60orders%60%2C%20run%20%60count(DISTINCT%20%22ShippedDate%22)%60%20and%20then%0A%20%20%20%20%60SELECT%20count(*)%20FROM%20(SELECT%20DISTINCT%20%22ShippedDate%22%20FROM%20orders)%60.%20Explain%20the%0A%20%20%20%20difference%20of%20one.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20%60DISTINCT%60%20you%20needed%20is%20usually%20a%20symptom%0A%0A%20%20%20%20A%20query%20returns%20duplicates%20for%20a%20reason%2C%20and%20there%20are%20three.%0A%0A%20%20%20%20-%20**The%20column%20genuinely%20repeats.**%2093%20customers%20live%20in%2021%20countries%2C%20so%20a%20list%20of%0A%20%20%20%20%20%20countries%20repeats.%20Here%20%60DISTINCT%60%20is%20the%20right%20tool%20and%20there%20is%20nothing%20to%0A%20%20%20%20%20%20investigate.%0A%20%20%20%20-%20**You%20joined%20to%20a%20table%20that%20has%20more%20than%20one%20matching%20row.**%20One%20order%20with%20six%0A%20%20%20%20%20%20lines%20becomes%20six%20rows%2C%20and%20%60DISTINCT%60%20collapses%20them%20back%20while%20quietly%20discarding%0A%20%20%20%20%20%20the%20reason%20there%20were%20six.%20Chapter%20C%20%C2%A719%20is%20that%20whole%20story.%0A%20%20%20%20-%20**The%20table%20has%20duplicate%20rows%20in%20it.**%20Somebody%20loaded%20the%20same%20file%20twice.%0A%20%20%20%20%20%20%60DISTINCT%60%20makes%20the%20symptom%20disappear%20from%20your%20screen%20and%20leaves%20it%20in%20the%20table%0A%20%20%20%20%20%20for%20the%20next%20person.%0A%0A%20%20%20%20A%20%60DISTINCT%60%20added%20to%20make%20a%20number%20look%20right%20is%20a%20note%20to%20yourself%20that%20you%20have%20not%0A%20%20%20%20found%20out%20why%20the%20number%20was%20wrong.%20Before%20you%20add%20one%2C%20run%20the%20query%20without%20it%20and%0A%20%20%20%20look%20at%20a%20set%20of%20the%20duplicates.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20take%20one%20%60%22OrderID%22%60%20from%20%60%22Order%20Details%22%60%20and%20select%20all%20its%20rows.%0A%20%20%20%20Now%20run%20%60SELECT%20DISTINCT%20%22OrderID%22%20FROM%20%22Order%20Details%22%20WHERE%20%22OrderID%22%20%3D%20%3Cthat%20id%3E%60.%0A%20%20%20%20You%20get%20one%20row.%20Name%20what%20you%20can%20no%20longer%20see.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20%60SELECT%20DISTINCT%20%22Country%22%60%20gives%2022%20rows%20and%20%60SELECT%20DISTINCT%20%22Country%22%2C%20%22City%22%60%0A%20%20%20%20%20%20%20gives%2070.%20Explain%20the%20difference%20to%20somebody%20who%20has%20never%20written%20SQL.%0A%20%20%20%202.%20%60count(DISTINCT%20%22Region%22)%60%20is%209%20and%20the%20distinct%20list%20is%2010%20rows%20long.%20Which%20number%0A%20%20%20%20%20%20%20goes%20in%20a%20memo%2C%20and%20what%20sentence%20has%20to%20go%20with%20it%3F%0A%20%20%20%203.%20A%20colleague%20adds%20%60DISTINCT%60%20and%20the%20row%20count%20drops%20from%204%2C000%20to%203%2C997.%20What%0A%20%20%20%20%20%20%20should%20they%20do%20next%3F%0A%20%20%20%204.%20When%20is%20%60DISTINCT%60%20the%20right%20answer%2C%20and%20when%20is%20it%20a%20sign%20that%20something%20needs%0A%20%20%20%20%20%20%20investigating%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2011.%20%60CASE%20WHEN%60%0A%0A%20%20%20%20%60CASE%20WHEN%60%20is%20SQL's%20%60if%60.%20It%20tests%20conditions%20in%20order%2C%20stops%20at%20the%20first%20one%20that%0A%20%20%20%20is%20true%2C%20and%20returns%20the%20value%20attached%20to%20it.%20It%20is%20how%20a%20continuous%20number%20becomes%20a%0A%20%20%20%20band%2C%20how%20a%20code%20becomes%20a%20label%2C%20and%20how%20you%20count%20something%20conditionally%20inside%20an%0A%20%20%20%20aggregate.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20shape%0A%0A%20%20%20%20A%20%60CASE%60%20expression%20is%20one%20column%20of%20the%20result.%20It%20runs%20once%20per%20row.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20CASE%20WHEN%20%22UnitPrice%22%20%3C%2020%20THEN%20'budget'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHEN%20%22UnitPrice%22%20%3C%2050%20THEN%20'mid'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ELSE%20'premium'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20END%20AS%20price_band%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_products%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20GROUP%20BY%20price_band%0A%20%20%20%20%20%20%20%20ORDER%20BY%20n_products%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%2077%20products%20come%20out%20as%2039%20budget%2C%2031%20mid%20and%207%20premium%2C%20divided%20up%20by%20a%20rule%20you%0A%20%20%20%20invented%20in%20the%20query.%20The%20rule%20is%20nowhere%20in%20the%20table%2C%20and%20anybody%20reading%20the%0A%20%20%20%20number%20has%20to%20be%20told%20what%20the%20boundaries%20were.%0A%0A%20%20%20%20%60GROUP%20BY%20price_band%60%20is%20%C2%A714%20arriving%20early.%20Read%20it%20as%20%22one%20row%20per%20band%22%20and%20come%0A%20%20%20%20back.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20band%20%60orders%60%20by%20freight%2C%20using%20100%20and%20400%20as%20the%20boundaries%2C%20and%0A%20%20%20%20count%20each%20band.%20Then%20move%20the%20lower%20boundary%20to%20150%20and%20state%20how%20much%20the%20picture%0A%20%20%20%20changed.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20first%20true%20branch%20wins%0A%0A%20%20%20%20Conditions%20are%20tested%20top%20to%20bottom%20and%20testing%20stops%20at%20the%20first%20hit.%20Swap%20two%20lines%0A%20%20%20%20that%20overlap%20and%20the%20answer%20changes%20without%20anything%20looking%20wrong.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20CASE%20WHEN%20%22UnitPrice%22%20%3C%2050%20THEN%20'mid'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHEN%20%22UnitPrice%22%20%3C%2020%20THEN%20'budget'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ELSE%20'premium'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20END%20AS%20price_band%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_products%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20GROUP%20BY%20price_band%0A%20%20%20%20%20%20%20%20ORDER%20BY%20n_products%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**The%20trap.**%20%60budget%60%20is%20gone.%20Every%20product%20under%2020%20is%20also%20under%2050%2C%20so%20the%20first%0A%20%20%20%20branch%20claims%20it%20and%20the%20second%20is%20unreachable.%20The%20query%20runs%2C%20returns%20two%20bands%0A%20%20%20%20instead%20of%20three%2C%20and%20the%20only%20sign%20is%20a%20category%20that%20silently%20holds%20zero%20rows.%0A%0A%20%20%20%20When%20bands%20overlap%2C%20order%20them%20from%20the%20narrowest%20condition%20to%20the%20widest.%20When%20they%0A%20%20%20%20do%20not%20overlap%2C%20the%20order%20does%20not%20matter%20and%20writing%20them%20in%20a%20sensible%20sequence%0A%20%20%20%20still%20helps%20whoever%20reads%20it.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20write%20a%20three-band%20%60CASE%60%20where%20the%20branches%20overlap%20on%20purpose%2C%20run%0A%20%20%20%20it%2C%20then%20reorder%20the%20branches%20and%20run%20it%20again.%20Predict%20the%20second%20result%20before%20you%0A%20%20%20%20look.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20No%20%60ELSE%60%20means%20%60NULL%60%0A%0A%20%20%20%20%60ELSE%60%20is%20optional.%20Leaving%20it%20out%20gives%20every%20unmatched%20row%20a%20%60NULL%60%20in%20that%20column.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20CASE%20WHEN%20%22UnitPrice%22%20%3C%2020%20THEN%20'budget'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHEN%20%22UnitPrice%22%20%3C%2050%20THEN%20'mid'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20END%20AS%20price_band%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_products%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20GROUP%20BY%20price_band%0A%20%20%20%20%20%20%20%20ORDER%20BY%20n_products%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%207%20premium%20products%20are%20still%20there%20and%20their%20band%20is%20%60NULL%60.%20In%20a%20grouped%20result%0A%20%20%20%20they%20are%20visible%20as%20a%20row%20labeled%20%60NULL%60%2C%20which%20is%20lucky.%20In%20a%20filtered%20one%20they%20are%0A%20%20%20%20gone%2C%20because%20%C2%A79%20already%20showed%20that%20a%20%60NULL%60%20fails%20every%20comparison.%0A%0A%20%20%20%20Write%20an%20%60ELSE%60%20every%20time%2C%20even%20where%20you%20are%20sure%20the%20branches%20cover%20everything.%0A%20%20%20%20%60ELSE%20'other'%60%20costs%20one%20line%20and%20turns%20a%20silent%20hole%20into%20a%20labeled%20one.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20wrap%20that%20query%20in%20%60SELECT%20sum(n_products)%20FROM%20(...)%20WHERE%20price_band%0A%20%20%20%20IS%20NOT%20NULL%60%20and%20watch%20the%20total%20come%20to%2070%20instead%20of%2077.%20Then%20put%20the%20%60ELSE%60%20back%0A%20%20%20%20and%20run%20it%20again.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60sum(CASE%20WHEN%20...%20THEN%201%20ELSE%200%20END)%60%20counts%20a%20condition%0A%0A%20%20%20%20This%20is%20the%20most%20used%20%60CASE%60%20in%20business%20SQL.%20It%20puts%20a%201%20on%20every%20row%20that%20matches%0A%20%20%20%20and%20a%200%20on%20every%20row%20that%20does%20not%2C%20and%20the%20sum%20is%20the%20count%20of%20matches.%20Because%20it%20is%0A%20%20%20%20an%20aggregate%20over%20the%20same%20rows%20as%20%60count(*)%60%2C%20you%20get%20the%20count%20and%20the%20share%20in%20one%0A%20%20%20%20query.%0A%0A%20%20%20%20An%20order%20is%20late%20when%20it%20shipped%20after%20the%20date%20it%20was%20required.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20AS%20n_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20sum(CASE%20WHEN%20%22ShippedDate%22%20%3E%20%22RequiredDate%22%20THEN%201%20ELSE%200%20END)%20AS%20late%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(100.0%20*%20sum(CASE%20WHEN%20%22ShippedDate%22%20%3E%20%22RequiredDate%22%20THEN%201%20ELSE%200%20END)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%20count(*)%2C%201)%20AS%20pct_late%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Of%2016%2C282%20orders%2C%203%2C755%20shipped%20late%2C%20which%20is%2023.1%20percent.%20That%20is%20a%20business%20fact%0A%20%20%20%20somebody%20would%20want%20on%20a%20slide%2C%20and%20it%20took%20one%20query.%0A%0A%20%20%20%20The%20%60100.0%60%20is%20deliberate.%20Written%20as%20%60100%60%2C%20both%20sides%20of%20the%20division%20are%20integers%20in%0A%20%20%20%20some%20engines%20and%20the%20answer%20comes%20back%20as%20a%20whole%20number%20of%20percent.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20get%20the%20count%20and%20the%20percentage%20of%20orders%20where%20freight%20was%20over%20400.%0A%20%20%20%20Then%20do%20the%20same%20thing%20with%20a%20%60WHERE%60%20clause%20and%20two%20separate%20queries%2C%20and%20state%20which%0A%20%20%20%20version%20you%20would%20rather%20maintain.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20What%20that%20%60ELSE%200%60%20did%20to%20the%2021%0A%0A%20%20%20%20%60ShippedDate%60%20is%20%60NULL%60%20for%2021%20orders.%20%60NULL%20%3E%20%22RequiredDate%22%60%20is%20%60NULL%60%2C%20which%20is%20not%0A%20%20%20%20true%2C%20so%20those%20rows%20fall%20into%20the%20%60ELSE%60%20and%20are%20counted%20as%200.%20They%20were%20counted%20as%0A%20%20%20%20not%20late.%0A%0A%20%20%20%20The%20query%20below%20counts%20the%20three%20groups%20separately%20with%20%60FILTER%20(WHERE%20...)%60%2C%20which%0A%20%20%20%20applies%20a%20condition%20to%20one%20aggregate%20and%20leaves%20the%20others%20alone.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20FILTER%20(WHERE%20%22ShippedDate%22%20%3C%3D%20%22RequiredDate%22)%20AS%20on_time%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20FILTER%20(WHERE%20%22ShippedDate%22%20%3E%20%20%22RequiredDate%22)%20AS%20late%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20FILTER%20(WHERE%20%22ShippedDate%22%20IS%20NULL)%20%20%20%20%20%20%20%20%20%20%20AS%20never_shipped%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20total%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20three%20counts%20are%2012%2C506%20on%20time%2C%203%2C755%20late%20and%2021%20never%20shipped%2C%20and%20they%20add%20to%0A%20%20%20%2016%2C282.%20The%20two-way%20split%20hid%20the%20third%20group%20inside%20the%20first%20one.%0A%0A%20%20%20%20**The%20trap.**%20Every%20two-branch%20%60CASE%60%20over%20a%20column%20that%20can%20be%20%60NULL%60%20puts%20the%0A%20%20%20%20unknowns%20in%20one%20branch%2C%20and%20it%20is%20always%20the%20%60ELSE%60.%20An%20order%20that%20never%20shipped%20is%0A%20%20%20%20the%20worst%20outcome%20the%20business%20has%20and%20this%20query%20filed%20it%20under%20%22on%20time%22.%0A%0A%20%20%20%20Write%20the%20third%20branch%3A%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20CASE%20WHEN%20%22ShippedDate%22%20IS%20NULL%20%20%20%20%20%20%20%20%20%20THEN%20'never%20shipped'%0A%20%20%20%20%20%20%20%20%20WHEN%20%22ShippedDate%22%20%3E%20%22RequiredDate%22%20THEN%20'late'%0A%20%20%20%20%20%20%20%20%20ELSE%20'on%20time'%0A%20%20%20%20END%0A%20%20%20%20%60%60%60%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20CASE%20WHEN%20%22ShippedDate%22%20IS%20NULL%20%20%20%20%20%20%20%20%20%20THEN%20'never%20shipped'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHEN%20%22ShippedDate%22%20%3E%20%22RequiredDate%22%20THEN%20'late'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ELSE%20'on%20time'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20END%20AS%20delivery_status%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_orders%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20GROUP%20BY%20delivery_status%0A%20%20%20%20%20%20%20%20ORDER%20BY%20n_orders%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20%60IS%20NULL%60%20branch%20goes%20first%20because%20the%20branches%20overlap%20in%20the%20way%20the%20previous%0A%20%20%20%20knowledge%20point%20described.%20An%20unshipped%20order%20fails%20the%20second%20test%2C%20so%20it%20would%20land%0A%20%20%20%20in%20%60ELSE%60%20if%20the%20first%20branch%20were%20not%20there%20to%20catch%20it.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20build%20the%20same%20three-way%20status%20for%20%60customers.Region%60%2C%20with%20branches%0A%20%20%20%20for%20%60NULL%60%2C%20for%20%60'Western%20Europe'%60%20and%20for%20everything%20else.%20Check%20the%20three%20counts%20add%0A%20%20%20%20to%2093.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20A%20%60CASE%60%20with%20three%20branches%20returns%20only%20two%20labels.%20What%20happened%3F%0A%20%20%20%202.%20Why%20does%20%60sum(CASE%20WHEN%20cond%20THEN%201%20ELSE%200%20END)%60%20give%20you%20a%20count%2C%20and%20what%20does%20it%0A%20%20%20%20%20%20%20give%20you%20that%20a%20%60WHERE%60%20clause%20does%20not%3F%0A%20%20%20%203.%20Your%20%60CASE%60%20has%20no%20%60ELSE%60.%20Where%20do%20the%20unmatched%20rows%20show%20up%20in%20a%20grouped%20result%2C%0A%20%20%20%20%20%20%20and%20where%20do%20they%20disappear%3F%0A%20%20%20%204.%20The%20on-time%20and%20late%20counts%20add%20to%2016%2C261%20and%20the%20table%20has%2016%2C282%20rows.%20Where%20are%0A%20%20%20%20%20%20%20the%20other%2021%2C%20and%20which%20of%20the%20two%20labels%20would%20a%20two-branch%20%60CASE%60%20have%20given%20them%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2012.%20Dates%3A%20truncating%2C%20differencing%2C%20grouping%20by%20month%0A%0A%20%20%20%20Two%20queries%20below%20run%20without%20an%20error%2C%20return%20a%20plausible%20number%2C%20and%20are%20wrong.%20Both%0A%20%20%20%20are%20wrong%20for%20the%20same%20reason%2C%20and%20it%20is%20the%20first%20thing%20to%20check%20about%20any%20date%20column%0A%20%20%20%20you%20are%20handed.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%20%C2%B7%20Building%20your%20product%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20timestamp%20with%20a%20clock%20time%0A%0A%20%20%20%20%60orders%60%20has%20three%20date%20columns%20and%20%60DESCRIBE%60%20calls%20all%20three%20%60TIMESTAMP%60.%20A%0A%20%20%20%20timestamp%20is%20a%20date%20and%20a%20time%20of%20day%2C%20down%20to%20the%20second.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20FILTER%20(WHERE%20%22OrderDate%22%20%3D%20date_trunc('day'%2C%20%22OrderDate%22))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20at_midnight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20FILTER%20(WHERE%20%22OrderDate%22%20%3C%3E%20date_trunc('day'%2C%20%22OrderDate%22))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20with_a_clock_time%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20total%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20min(%22OrderDate%22)%20AS%20earliest%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20max(%22OrderDate%22)%20AS%20latest%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Exactly%20830%20orders%20sit%20at%20midnight%20and%2015%2C452%20carry%20a%20real%20time%20of%20day.%20The%20830%20are%20the%0A%20%20%20%20block%20from%20%60OrderID%60%2010248%20to%2011077%2C%20the%20original%20Northwind%20rows%20that%20came%20with%20a%20date%0A%20%20%20%20and%20no%20time%3B%20everything%20after%20that%20was%20generated%20with%20a%20clock.%0A%0A%20%20%20%20The%20data%20runs%20from%202012-07-10%20to%202023-10-28.%20Anything%20you%20read%20about%20Northwind%0A%20%20%20%20elsewhere%20is%20written%20against%201996%20to%201998%20and%20returns%20zero%20rows%20here.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20confirm%20the%20830%20by%20running%0A%20%20%20%20%60SELECT%20min(%22OrderID%22)%2C%20max(%22OrderID%22)%2C%20count(*)%20FROM%20orders%20WHERE%20%22OrderDate%22%20%3D%0A%20%20%20%20date_trunc('day'%2C%20%22OrderDate%22)%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60%3D%60%20against%20a%20timestamp%0A%0A%20%20%20%20How%20many%20orders%20were%20placed%20on%204%20July%202016%3F%20The%20obvious%20query%20and%20the%20right%20one%20differ%0A%20%20%20%20by%20six%20characters.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'WHERE%20%22OrderDate%22%20%3D%20DATE%20''2016-07-04'''%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20%3D%20DATE%20'2016-07-04')%20AS%20rows_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'WHERE%20%22OrderDate%22%3A%3ADATE%20%3D%20DATE%20''2016-07-04'''%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%3A%3ADATE%20%3D%20DATE%20'2016-07-04')%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20first%20form%20finds%20one%20row%20and%20the%20second%20finds%206.%20The%20first%20query%20compared%20a%0A%20%20%20%20timestamp%20to%20a%20date%2C%20which%20SQL%20reads%20as%0A%20%20%20%20a%20comparison%20against%20midnight%20on%20that%20date%2C%20so%20it%20found%20the%20single%20order%20that%20happened%0A%20%20%20%20to%20have%20no%20clock%20time.%20It%20is%20off%20by%20five%20sixths%20and%20there%20is%20nothing%20on%20the%20screen%20to%0A%20%20%20%20say%20so.%0A%0A%20%20%20%20%60%3A%3ADATE%60%20throws%20the%20time%20away%20before%20comparing.%20%60CAST(%22OrderDate%22%20AS%20DATE)%60%20is%20the%20same%0A%20%20%20%20thing%20written%20portably.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20both%20forms%20for%202016-07-08%20and%20for%202017-03-15.%20Predict%20which%20dates%0A%20%20%20%20give%20you%20the%20same%20answer%20twice%20before%20you%20run%20them.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60BETWEEN%60%20on%20a%20timestamp%0A%0A%20%20%20%20%C2%A77%20said%20%60BETWEEN%60%20includes%20both%20ends%2C%20and%20on%20a%20price%20that%20was%20fine.%20On%20a%20timestamp%20the%0A%20%20%20%20upper%20end%20is%20midnight%2C%20so%20a%20whole%20day%20goes%20missing%20from%20a%20range%20whose%20name%20contains%0A%20%20%20%20that%20day.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'BETWEEN%20''2016-07-01''%20AND%20''2016-07-31'''%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20BETWEEN%20DATE%20'2016-07-01'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20DATE%20'2016-07-31')%20AS%20rows_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'%3E%3D%20''2016-07-01''%20AND%20%3C%20''2016-08-01'''%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-07-01'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20%22OrderDate%22%20%3C%20%20DATE%20'2016-08-01')%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20two%20forms%20return%20146%20and%20149.%20Four%20orders%20were%20placed%20on%2031%20July%2C%20three%20of%20them%0A%20%20%20%20carry%20a%20clock%20time%2C%20and%20those%20three%20are%20what%20fell%20out.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22OrderID%22%2C%20%22OrderDate%22%2C%20%22ShipCountry%22%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20%3E%20%20DATE%20'2016-07-31'%0A%20%20%20%20%20%20%20%20%20%20AND%20%22OrderDate%22%20%3C%20%20DATE%20'2016-08-01'%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22OrderDate%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**The%20trap.**%20A%20monthly%20report%20built%20on%20%60BETWEEN%60%20undercounts%20every%20month%2C%20by%20however%0A%20%20%20%20many%20orders%20came%20in%20after%20midnight%20on%20the%20last%20day.%20The%20shortfall%20here%20is%20about%202%0A%20%20%20%20percent%2C%20it%20is%20in%20the%20same%20direction%20every%20month%2C%20and%20no%20engine%20reports%20it.%0A%0A%20%20%20%20Write%20date%20ranges%20half%20open%3A%20%60%3E%3D%60%20the%20start%20and%20%60%3C%60%20the%20day%20after%20the%20end.%20The%20upper%0A%20%20%20%20bound%20is%20a%20date%20you%20are%20excluding%2C%20which%20reads%20oddly%20for%20about%20a%20week%20and%20then%20stops%0A%20%20%20%20being%20a%20decision%20you%20make%20each%20time.%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-07-01'%20AND%20%22OrderDate%22%20%3C%20DATE%20'2016-08-01'%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20count%20the%20orders%20in%20February%202016%20with%20%60BETWEEN%60%20and%20with%20the%20half-open%0A%20%20%20%20form.%20Then%20do%202016-02-01%20to%202016-02-29%20and%20explain%20why%20February%20is%20the%20month%20where%20this%0A%20%20%20%20also%20goes%20wrong%20for%20a%20second%20reason.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Truncating%20with%20%60date_trunc%60%0A%0A%20%20%20%20%60date_trunc('month'%2C%20ts)%60%20returns%20the%20first%20instant%20of%20that%20month.%20Every%20timestamp%20in%0A%20%20%20%20July%202016%20truncates%20to%20%602016-07-01%2000%3A00%3A00%60%2C%20which%20is%20what%20makes%20grouping%20by%20month%0A%20%20%20%20work%20at%20all.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20date_trunc('month'%2C%20%22OrderDate%22)%20AS%20month_start%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_orders%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-07-01'%20AND%20%22OrderDate%22%20%3C%20DATE%20'2017-01-01'%0A%20%20%20%20%20%20%20%20GROUP%20BY%20month_start%0A%20%20%20%20%20%20%20%20ORDER%20BY%20month_start%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20July's%20149%20matches%20the%20half-open%20count%20above%2C%20which%20is%20the%20check%20worth%20doing%20whenever%0A%20%20%20%20you%20group%20by%20a%20truncated%20date.%0A%0A%20%20%20%20The%20units%20are%20%60'year'%60%2C%20%60'quarter'%60%2C%20%60'month'%60%2C%20%60'week'%60%2C%20%60'day'%60%20and%20%60'hour'%60.%0A%20%20%20%20%60strftime%60%20returns%20a%20string%20instead%2C%20which%20is%20what%20a%20chart%20axis%20wants.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20strftime(%22OrderDate%22%2C%20'%25Y-%25m')%20AS%20month_label%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_orders%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-07-01'%20AND%20%22OrderDate%22%20%3C%20DATE%20'2016-10-01'%0A%20%20%20%20%20%20%20%20GROUP%20BY%20month_label%0A%20%20%20%20%20%20%20%20ORDER%20BY%20month_label%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60'%25Y-%25m'%60%20sorts%20correctly%20as%20text%20because%20the%20year%20comes%20first.%20%60'%25m%2F%25Y'%60%20does%20not%2C%20and%0A%20%20%20%20a%20chart%20built%20on%20it%20puts%20January%20of%20every%20year%20next%20to%20each%20other.%0A%0A%20%20%20%20**The%20trap.**%20Grouping%20on%20the%20raw%20timestamp%20looks%20like%20it%20worked%20and%20gives%20you%20one%0A%20%20%20%20group%20per%20second.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'GROUP%20BY%20%22OrderDate%22'%20AS%20grouped_on%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(SELECT%20%22OrderDate%22%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20%22OrderDate%22))%20AS%20n_groups%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'GROUP%20BY%20%22OrderDate%22%3A%3ADATE'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(SELECT%20%22OrderDate%22%3A%3ADATE%20FROM%20orders%20GROUP%20BY%201))%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'GROUP%20BY%20date_trunc(''month''%2C%20%22OrderDate%22)'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(SELECT%20date_trunc('month'%2C%20%22OrderDate%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20GROUP%20BY%201))%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Grouping%20on%20the%20raw%20timestamp%20gives%2015%2C932%20groups%20over%2016%2C282%20rows%2C%20against%204%2C053%20days%0A%20%20%20%20and%20136%20months%20for%20the%20other%20two.%20The%20first%20result%20is%20a%20table%20with%20almost%20one%20row%20per%0A%20%20%20%20order%2C%20and%20on%20a%20screen%20it%20looks%20like%20a%20very%20detailed%20report.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20count%20orders%20per%20year%20with%20%60date_trunc%60%2C%20then%20again%20with%0A%20%20%20%20%60date_part('year'%2C%20%22OrderDate%22)%60.%20Confirm%20the%20two%20agree%2C%20and%20state%20which%20one%20you%20would%0A%20%20%20%20put%20on%20the%20x%20axis%20of%20a%20chart.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Differencing%20with%20%60date_diff%60%0A%0A%20%20%20%20%60date_diff(unit%2C%20start%2C%20end)%60%20gives%20the%20number%20of%20unit%20boundaries%20between%20the%20two.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22OrderID%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22OrderDate%22%3A%3ADATE%20%20%20AS%20ordered%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22ShippedDate%22%3A%3ADATE%20AS%20shipped%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20date_diff('day'%2C%20%22OrderDate%22%2C%20%22ShippedDate%22)%20AS%20days_to_ship%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(%22Freight%22%2C%202)%20AS%20freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22ShipCountry%22%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22OrderID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20five%20orders%20went%20to%20four%20countries%20and%20took%2012%2C%205%2C%204%2C%207%20and%202%20days%20to%20ship.%20These%0A%20%20%20%20rows%20come%20back%20throughout%20the%20course.%20Read%20them%20once%20by%20eye.%0A%0A%20%20%20%20**The%20trap.**%20%60date_diff('day'%2C%20...)%60%20counts%20midnight%20crossings%2C%20so%20two%20timestamps%20two%0A%20%20%20%20hours%20apart%20return%201%20day%20when%20the%20midnight%20is%20between%20them%2C%20and%20two%20timestamps%2022%20hours%0A%20%20%20%20apart%20return%200%20days%20when%20it%20is%20not.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20date_diff('day'%2C%20TIMESTAMP%20'2016-07-04%2023%3A00%3A00'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TIMESTAMP%20'2016-07-05%2001%3A00%3A00')%20AS%20two_hours_apart%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20date_diff('day'%2C%20TIMESTAMP%20'2016-07-04%2001%3A00%3A00'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TIMESTAMP%20'2016-07-04%2023%3A00%3A00')%20AS%20twenty_two_hours_apart%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20That%20is%20the%20right%20answer%20for%20%22how%20many%20days%20later%22%2C%20and%20it%20is%20the%20wrong%20one%20for%20%22how%0A%20%20%20%20long%20did%20it%20take%22.%20For%20elapsed%20time%2C%20turn%20the%20gap%20into%20seconds%20with%20%60epoch()%60%20and%0A%20%20%20%20divide.%20%60epoch(%22ShippedDate%22%20-%20%22OrderDate%22)%20%2F%2086400%60%20is%20the%20gap%20in%20days%2C%20and%20dividing%0A%20%20%20%20by%203%2C600%20gives%20hours.%20Keep%20the%20%60epoch()%60%2C%20because%20the%20subtraction%20alone%20returns%20an%0A%20%20%20%20%60INTERVAL%60%2C%20which%20a%20marimo%20SQL%20cell%20cannot%20turn%20into%20a%20table.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20round(epoch(TIMESTAMP%20'2016-07-05%2001%3A00%3A00'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%20TIMESTAMP%20'2016-07-04%2023%3A00%3A00')%20%2F%2086400%2C%202)%20AS%20two_hours_apart%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(epoch(TIMESTAMP%20'2016-07-04%2023%3A00%3A00'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%20TIMESTAMP%20'2016-07-04%2001%3A00%3A00')%20%2F%2086400%2C%202)%20AS%20twenty_two_hours_apart%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Measured%20this%20way%2C%20the%20same%20two%20pairs%20are%200.08%20and%200.92%20days%20apart.%20Averaged%20over%20the%0A%20%20%20%20warehouse%2C%20the%20day%20count%20from%20%60date_diff%60%20is%20stable%20enough%20to%20quote.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(%22ShippedDate%22)%20AS%20shipped_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(avg(date_diff('day'%2C%20%22OrderDate%22%2C%20%22ShippedDate%22))%2C%202)%20AS%20mean_days%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20median(date_diff('day'%2C%20%22OrderDate%22%2C%20%22ShippedDate%22))%20AS%20median_days%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20mean%20is%207.85%20days%20and%20the%20median%20is%206%2C%20over%20the%2016%2C261%20orders%20that%20shipped.%20The%2021%0A%20%20%20%20that%20never%20shipped%20are%20absent%20from%20both%20numbers%2C%20which%20%C2%A713%20explains%20and%20%C2%A79%20already%20warned%0A%20%20%20%20about.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20find%20the%20five%20orders%20with%20the%20longest%20gap%20between%20%60%22OrderDate%22%60%20and%0A%20%20%20%20%60%22ShippedDate%22%60.%20Then%20check%20whether%20any%20of%20them%20is%20one%20of%20the%2021%2C%20and%20state%20what%20your%0A%20%20%20%20query%20did%20with%20those%20rows.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Pulling%20one%20piece%20out%0A%0A%20%20%20%20%60date_part%60%20returns%20a%20number%20and%20%60dayname%60%20returns%20a%20word.%20Use%20both%20for%20grouping%20and%0A%20%20%20%20labeling.%20A%20filter%20written%20on%20a%20date%20part%20has%20to%20compute%20the%20part%20for%20every%20row%20before%0A%20%20%20%20it%20can%20compare%20anything%2C%20which%20is%20why%20%C2%A712%20opened%20on%20ranges.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22OrderID%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22OrderDate%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20date_part('year'%2C%20%20%22OrderDate%22)%20AS%20yr%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20date_part('month'%2C%20%22OrderDate%22)%20AS%20mo%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayname(%22OrderDate%22)%20%20%20%20%20%20%20%20%20%20%20%20AS%20weekday%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20current_date%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20today%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22OrderID%22%20BETWEEN%2010248%20AND%2010250%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22OrderID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60current_date%60%20is%20today%20according%20to%20the%20machine%20running%20the%20query%2C%20so%20a%20report%20that%0A%20%20%20%20uses%20it%20gives%20a%20different%20answer%20tomorrow.%20That%20is%20usually%20what%20you%20want%20and%20it%20makes%0A%20%20%20%20the%20result%20impossible%20to%20reproduce%2C%20so%20pin%20the%20date%20in%20anything%20you%20are%20asked%20to%0A%20%20%20%20defend.%0A%0A%20%20%20%20Date%20functions%20are%20where%20engines%20disagree%20most%2C%20and%20the%20names%20are%20the%20disagreement.%0A%20%20%20%20SQL%20Server%20writes%20%60GETDATE()%60%20and%20%60DATEDIFF(DAY%2C%20a%2C%20b)%60%20where%20DuckDB%20writes%0A%20%20%20%20%60current_date%60%20and%20%60date_diff('day'%2C%20a%2C%20b)%60.%20Check%20the%20function%20names%20first%20whenever%20a%0A%20%20%20%20query%20written%20for%20one%20engine%20will%20not%20parse%20in%20another.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20count%20the%20orders%20by%20day%20of%20the%20week%20and%20judge%20whether%20the%20pattern%20is%20a%0A%20%20%20%20fact%20about%20the%20business%20or%20a%20fact%20about%20how%20this%20data%20was%20generated.%20%60dayname%60%20and%20a%0A%20%20%20%20%60GROUP%20BY%60%20are%20all%20you%20need.%20Type%20the%20number%20of%20orders%20placed%20on%20a%20Sunday%20into%20the%20box%0A%20%20%20%20at%20the%20end%20of%20this%20section.%20The%20box%20answers%20only%20when%20you%20run%20this%20chapter%20on%20your%20own%0A%20%20%20%20computer.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20%60WHERE%20%22OrderDate%22%20%3D%20DATE%20'2016-07-04'%60%20returns%20one%20row.%20Why%20is%20that%20number%20wrong%2C%0A%20%20%20%20%20%20%20and%20why%20does%20nothing%20warn%20you%3F%0A%20%20%20%202.%20Write%20the%20half-open%20range%20for%20the%20whole%20of%20March%202016.%20What%20is%20the%20upper%20bound%2C%20and%0A%20%20%20%20%20%20%20why%20is%20it%20a%20date%20in%20April%3F%0A%20%20%20%203.%20A%20monthly%20chart%20built%20with%20%60BETWEEN%60%20and%20one%20built%20with%20%60%3E%3D%60%20and%20%60%3C%60%20differ%20by%20about%0A%20%20%20%20%20%20%203%20percent%20in%20most%20months.%20Which%20is%20right%2C%20and%20how%20would%20you%20show%20somebody%3F%0A%20%20%20%204.%20%60date_diff('day'%2C%20...)%60%20between%2011pm%20and%201am%20the%20next%20night%20returns%201.%20When%20is%20that%0A%20%20%20%20%20%20%20the%20answer%20you%20want%2C%20and%20when%20is%20it%20not%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(mo)%3A%0A%20%20%20%20found_it_box%20%3D%20mo.ui.text(label%3D%22Your%20number%22)%0A%20%20%20%20found_it_box%0A%20%20%20%20return%20(found_it_box%2C)%0A%0A%0A%40app.cell%0Adef%20_(found_it_box%2C%20mo)%3A%0A%20%20%20%20import%20base64%20as%20_base64%0A%20%20%20%20import%20hashlib%20as%20_hashlib%0A%0A%20%20%20%20_key%20%3D%20found_it_box.value.strip().replace(%22%2C%22%2C%20%22%22)%0A%20%20%20%20_lock%20%3D%20%22e2167e3c1b1afe4fc1a81a51d3c18ed85f31e8449288ad63f80a6b0d2a5ed179%22%0A%20%20%20%20_box%20%3D%20_base64.b64decode(%228gIgJQZaYfuZ1WiU9nvCdQv0dsxrTS8xqqDLmVS8gr2WKM3IN6d3IW%2BDEdrR9IsIteeAQAsbmk8zPtNQuBDJ7%2B3%2FvdPoPhwXbs4IxiIGMqZG8maz1YkmgIa6ZzPrOAjItfxvr6CdYUpG7lIIHxXdA3fyKluXWKkGXJ7Obupb1hBaxitUZmI12yDCMFeQqCBK0ed0yA6VZWNp0XqoyySunSbNVCjGCh2VhnrjhfyL1NUVL67fdNtLZwgIeIPeNhty4srVBb4%3D%22)%0A%20%20%20%20_stream%20%3D%20b%22%22.join(%0A%20%20%20%20%20%20%20%20_hashlib.sha256(f%22pad%3A%7B_key%7D%3A%7B_i%7D%22.encode()).digest()%0A%20%20%20%20%20%20%20%20for%20_i%20in%20range(len(_box)%20%2F%2F%2032%20%2B%201)%0A%20%20%20%20)%0A%20%20%20%20_opened%20%3D%20_hashlib.sha256(f%22lock%3A%7B_key%7D%22.encode()).hexdigest()%20%3D%3D%20_lock%0A%20%20%20%20mo.md(bytes(_a%20%5E%20_b%20for%20_a%2C%20_b%20in%20zip(_box%2C%20_stream)).decode())%20if%20_opened%20else%20None%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2013.%20Aggregate%20functions%0A%0A%20%20%20%20An%20aggregate%20function%20reads%20many%20rows%20and%20returns%20one%20value.%20%60count%60%2C%20%60sum%60%2C%20%60avg%60%2C%0A%20%20%20%20%60min%60%2C%20%60max%60%20and%20%60median%60%20are%20the%20six%20that%20cover%20most%20business%20questions.%20On%20their%20own%0A%20%20%20%20they%20collapse%20the%20whole%20table%20to%20a%20single%20row%3B%20with%20%C2%A714's%20%60GROUP%20BY%60%20they%20collapse%20each%0A%20%20%20%20group%20to%20a%20row.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20From%20many%20rows%20to%20a%20single%20row%0A%0A%20%20%20%20This%20runs%20six%20aggregates%20over%20all%2016%2C282%20orders%20in%20one%20query.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20n_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(sum(%22Freight%22)%2C%202)%20%20%20%20%20AS%20total_freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(avg(%22Freight%22)%2C%202)%20%20%20%20%20AS%20mean_freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(median(%22Freight%22)%2C%202)%20%20AS%20median_freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(min(%22Freight%22)%2C%202)%20%20%20%20%20AS%20cheapest%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(max(%22Freight%22)%2C%202)%20%20%20%20%20AS%20dearest%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20result%20is%20one%20row%20of%20six%20columns%2C%20with%20no%20way%20back%20to%20the%20rows%20that%20produced%20it.%0A%20%20%20%20That%20is%20the%20trade%20an%20aggregate%20makes.%0A%0A%20%20%20%20You%20cannot%20put%20a%20plain%20column%20next%20to%20an%20aggregate.%20%60SELECT%20%22ShipCountry%22%2C%20count(*)%60%0A%20%20%20%20with%20no%20%60GROUP%20BY%60%20asks%20for%20one%20country%20name%20to%20stand%20for%2016%2C282%20rows%2C%20and%20%C2%A714%20shows%0A%20%20%20%20the%20error%20you%20get.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20get%20the%20same%20six%20numbers%20for%20%60products.UnitPrice%60.%20Then%20state%20which%20of%0A%20%20%20%20the%20six%20you%20would%20put%20in%20a%20one-sentence%20description%20of%20the%20catalog.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60count(*)%60%20and%20%60count(column)%60%20are%20different%20questions%0A%0A%20%20%20%20%60count(*)%60%20counts%20rows.%20%60count(x)%60%20counts%20rows%20where%20%60x%60%20is%20not%20%60NULL%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20rows_in_table%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(%22Freight%22)%20%20%20%20%20AS%20freight_present%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(%22ShippedDate%22)%20AS%20ship_date_present%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20three%20counts%20are%2016%2C282%2C%2016%2C282%20and%2016%2C261.%20The%20last%20one%20is%2021%20short%20because%2021%0A%20%20%20%20orders%20never%20shipped%2C%20and%20subtracting%20the%20two%20is%20the%20standard%20way%20to%20count%20NULLs%20in%20a%0A%20%20%20%20column.%0A%0A%20%20%20%20**The%20trap.**%20%22How%20many%20orders%20do%20we%20have%22%20answered%20with%20%60count(%22ShippedDate%22)%60%20gives%0A%20%20%20%2016%2C261.%20It%20is%20a%20real%20number%2C%20it%20is%20close%20to%20right%2C%20and%20it%20answers%20a%20different%20question.%0A%20%20%20%20Use%20%60count(*)%60%20for%20rows%20and%20%60count(column)%60%20when%20the%20absence%20of%20a%20value%20is%20the%20thing%0A%20%20%20%20you%20are%20measuring.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20on%20%60customers%60%2C%20run%20%60count(*)%60%2C%20%60count(%22Region%22)%60%20and%20%60count(%22Fax%22)%60.%0A%20%20%20%20One%20of%20those%20three%20columns%20is%20much%20emptier%20than%20the%20others.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Aggregates%20skip%20%60NULL%60%0A%0A%20%20%20%20%60employees.ReportsTo%60%20has%20eight%20values%20and%20one%20%60NULL%60.%20%60avg%60%20sums%20the%20eight%20and%20divides%0A%20%20%20%20by%208.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20rows_%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(%22ReportsTo%22)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20non_null%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20sum(%22ReportsTo%22)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20total%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(avg(%22ReportsTo%22)%2C%203)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20avg_%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(sum(%22ReportsTo%22)%20*%201.0%20%2F%20count(*)%2C%203)%20%20AS%20divided_by_all_rows%0A%20%20%20%20%20%20%20%20FROM%20employees%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20two%20numbers%20are%203.125%20and%202.778.%20%60avg%60%20gave%20the%20first%2C%20and%20the%20second%20is%20what%20you%0A%20%20%20%20get%20if%20you%20decide%20a%20missing%20manager%20counts%20as%20a%20zero.%0A%0A%20%20%20%20Neither%20is%20wrong%20as%20arithmetic.%20They%20answer%20%22the%20average%20among%20people%20who%20have%20a%0A%20%20%20%20manager%22%20and%20%22the%20average%20if%20we%20count%20the%20VP%20as%20reporting%20to%20nobody%2C%20scored%20zero%22%2C%0A%20%20%20%20and%20only%20one%20of%20those%20is%20a%20sentence%20anybody%20would%20say%20out%20loud.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20compute%20the%20average%20days%20to%20ship%20two%20ways%2C%20once%20with%20%60avg%60%20over%20all%0A%20%20%20%2016%2C282%20orders%20and%20once%20after%20%60coalesce%60ing%20the%20missing%20ship%20dates%20to%20the%20order%20date.%0A%20%20%20%20State%20which%20of%20the%20two%20you%20would%20defend%20and%20to%20whom.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Aggregates%20over%20no%20rows%0A%0A%20%20%20%20A%20filter%20that%20matches%20nothing%20still%20returns%20a%20row%2C%20and%20what%20is%20in%20it%20depends%20on%20the%0A%20%20%20%20function.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20n_rows%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20sum(%22Freight%22)%20%20%20%20%20%20%20%20%20%20%20AS%20sum_freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20avg(%22Freight%22)%20%20%20%20%20%20%20%20%20%20%20AS%20avg_freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20coalesce(sum(%22Freight%22)%2C%200)%20AS%20sum_with_default%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22ShipCountry%22%20%3D%20'Atlantis'%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Zero%20rows%20matched.%20%60count(*)%60%20says%200%2C%20and%20%60sum%60%20and%20%60avg%60%20both%20say%20%60NULL%60%2C%20because%0A%20%20%20%20there%20was%20nothing%20to%20add%20and%20nothing%20to%20divide.%0A%0A%20%20%20%20**The%20trap.**%20A%20dashboard%20tile%20showing%20total%20revenue%20for%20a%20region%20with%20no%20sales%20prints%0A%20%20%20%20a%20blank%20instead%20of%20a%20zero%2C%20and%20a%20blank%20reads%20as%20%22the%20query%20broke%22%20to%20whoever%20is%20looking%0A%20%20%20%20at%20it.%20%60coalesce(sum(x)%2C%200)%60%20is%20the%20fix%2C%20and%20it%20is%20one%20of%20the%20places%20where%20replacing%20a%0A%20%20%20%20%60NULL%60%20with%20a%20zero%20is%20honest%2C%20because%20a%20sum%20over%20nothing%20genuinely%20is%20nothing.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20same%20query%20for%20%60'France'%60%20and%20for%20a%20misspelling%20of%20it.%20Compare%0A%20%20%20%20the%20four%20columns%20in%20each%20result.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20average%20is%20not%20the%20typical%0A%0A%20%20%20%20%60avg%60%20is%20pulled%20by%20anything%20extreme%20and%20%60median%60%20is%20not.%20Where%20the%20two%20disagree%2C%0A%20%20%20%20something%20in%20the%20data%20sits%20a%20long%20way%20from%20the%20rest.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AS%20n_products%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(avg(%22UnitPrice%22)%2C%202)%20%20AS%20mean_price%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(median(%22UnitPrice%22)%2C%202)%20AS%20median_price%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(max(%22UnitPrice%22)%2C%202)%20%20AS%20most_expensive%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20mean%20is%2028.87%20and%20the%20median%20is%2019.50.%20More%20than%20half%20the%20catalog%20is%20under%2020%20and%0A%20%20%20%20the%20mean%20is%20above%2028%20because%20%60C%C3%B4te%20de%20Blaye%60%20at%20263.50%20is%20pulling%20on%20it.%0A%0A%20%20%20%20Freight%20behaves%20differently%2C%20with%20a%20mean%20of%20248.59%20and%20a%20median%20of%20245.25.%20That%20is%20a%0A%20%20%20%20symmetric%20spread%20with%20no%20long%20tail.%0A%0A%20%20%20%20Report%20both%20when%20they%20disagree%2C%20and%20state%20which%20one%20the%20sentence%20is%20about.%20%22Our%20average%0A%20%20%20%20product%20costs%2028.87%22%20and%20%22half%20our%20products%20cost%20under%2019.50%22%20are%20both%20true%20and%20they%0A%20%20%20%20would%20send%20a%20buyer%20in%20different%20directions.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20compute%20the%20mean%20and%20median%20of%20%60%22Quantity%22%60%20in%20%60%22Order%20Details%22%60.%20Then%0A%20%20%20%20do%20the%20same%20after%20excluding%20the%20largest%201%20percent%2C%20and%20state%20what%20changed.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20%60count(*)%60%20is%2016%2C282%20and%20%60count(%22ShippedDate%22)%60%20is%2016%2C261.%20What%20is%20the%2021%2C%20and%20which%0A%20%20%20%20%20%20%20of%20the%20two%20counts%20answers%20%22how%20many%20orders%20did%20we%20take%22%3F%0A%20%20%20%202.%20%60avg%60%20on%20a%20column%20with%20missing%20values%20divides%20by%20what%3F%0A%20%20%20%203.%20A%20total%20shows%20blank%20instead%20of%200.%20What%20did%20the%20query%20do%2C%20and%20what%20would%20you%20add%3F%0A%20%20%20%204.%20Mean%2028.87%2C%20median%2019.50%2C%20max%20263.50.%20Describe%20the%20price%20list%20in%20one%20sentence%2C%20and%0A%20%20%20%20%20%20%20state%20which%20number%20you%20left%20out%20and%20why.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2014.%20%60GROUP%20BY%60%0A%0A%20%20%20%20%60GROUP%20BY%60%20splits%20the%20rows%20into%20groups%20and%20runs%20the%20aggregates%20once%20per%20group.%20One%20row%0A%20%20%20%20comes%20back%20for%20each%20distinct%20combination%20of%20the%20grouping%20columns%2C%20and%20every%20row%20of%20the%0A%20%20%20%20table%20lands%20in%20exactly%20one%20group.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20One%20row%20per%20distinct%20value%0A%0A%20%20%20%20%C2%A713%20collapsed%2016%2C282%20orders%20to%20one%20row.%20Grouping%20by%20country%20collapses%20them%20to%2021.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ShipCountry%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(avg(%22Freight%22)%2C%202)%20AS%20avg_freight%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20GROUP%20BY%20%22ShipCountry%22%0A%20%20%20%20%20%20%20%20ORDER%20BY%20n_orders%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%2010%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20That%20is%20the%20top%2010%20of%2021.%20The%20groups%20partition%20the%20table%2C%20so%20the%20counts%20add%20back%20up%20to%0A%20%20%20%20the%20row%20count%20you%20started%20with%2C%20and%20checking%20that%20is%20the%20cheapest%20audit%20in%20SQL.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20%20AS%20n_groups%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20sum(n)%20%20%20%20AS%20rows_covered%0A%20%20%20%20%20%20%20%20FROM%20(SELECT%20%22ShipCountry%22%2C%20count(*)%20AS%20n%20FROM%20orders%20GROUP%20BY%20%22ShipCountry%22)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20There%20are%2021%20groups%20covering%2016%2C282%20rows%2C%20which%20is%20every%20row%20in%20%60orders%60.%20A%20grouping%0A%20%20%20%20column%20with%20%60NULL%60%20in%20it%20gets%20a%20%60NULL%60%20group%20and%20is%20still%20counted%2C%20so%20the%20total%20holds%0A%20%20%20%20there%20too.%0A%0A%20%20%20%20%23%23%23%20In%20Excel%20terms%0A%0A%20%20%20%20This%20is%20a%20pivot%20table.%20The%20grouping%20columns%20are%20what%20you%20drag%20into%20Rows%2C%20the%20aggregates%0A%20%20%20%20are%20what%20you%20drag%20into%20Values%2C%20and%20%60count(*)%60%20is%20Count%20of%20anything.%20The%20difference%20is%0A%20%20%20%20that%20a%20pivot%20table%20decides%20the%20aggregate%20for%20you%20based%20on%20the%20column%20type%2C%20and%20here%20you%0A%20%20%20%20name%20it%2C%20so%20%60sum%60%20of%20an%20id%20column%20can%20only%20happen%20if%20you%20ask%20for%20it.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20count%20orders%20per%20%60%22ShipVia%22%60%20on%20%60orders%60%2C%20then%20run%20the%20same%20two-number%0A%20%20%20%20audit%20and%20confirm%20the%20counts%20add%20to%2016%2C282.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Every%20selected%20column%20is%20grouped%20or%20aggregated%0A%0A%20%20%20%20A%20grouped%20result%20has%20one%20row%20per%20group%2C%20so%20every%20column%20has%20to%20be%20something%20a%20group%20can%0A%20%20%20%20answer.%20A%20grouping%20column%20can%2C%20because%20the%20whole%20group%20shares%20it.%20An%20aggregate%20can%2C%0A%20%20%20%20because%20it%20reads%20the%20whole%20group.%20A%20plain%20column%20cannot.%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20SELECT%20%22CategoryID%22%2C%20%22ProductName%22%2C%20count(*)%0A%20%20%20%20FROM%20products%0A%20%20%20%20GROUP%20BY%20%22CategoryID%22%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%20Binder%20Error%3A%20column%20%22ProductName%22%20must%20appear%20in%20the%20GROUP%20BY%20clause%20or%20must%20be%0A%20%20%20%20part%20of%20an%20aggregate%20function.%0A%20%20%20%20Either%20add%20it%20to%20the%20GROUP%20BY%20list%2C%20or%20use%20%22ANY_VALUE(ProductName)%22%20if%20the%20exact%0A%20%20%20%20value%20of%20%22ProductName%22%20is%20not%20important.%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20The%20group%20for%20category%201%20holds%2012%20products%20with%2012%20different%20names%2C%20and%20the%20query%20asked%0A%20%20%20%20for%20one%20name.%20DuckDB%20will%20not%20guess%2C%20and%20the%20message%20tells%20you%20the%20two%20ways%20to%20make%0A%20%20%20%20the%20query%20answerable.%0A%0A%20%20%20%20Three%20ways%20forward%2C%20and%20they%20answer%20different%20questions%3A%20add%20the%20column%20to%20the%0A%20%20%20%20%60GROUP%20BY%60%20and%20get%20a%20finer%20grouping%2C%20wrap%20it%20in%20an%20aggregate%20such%20as%20%60min%60%20or%0A%20%20%20%20%60any_value%60%2C%20or%20drop%20it.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20that%20query%20and%20read%20the%20error.%20Then%20fix%20it%20three%20ways%20and%20explain%20what%0A%20%20%20%20each%20fix%20means%20in%20English.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Grouping%20on%20more%20than%20one%20column%0A%0A%20%20%20%20Two%20grouping%20columns%20make%20one%20group%20per%20combination%20that%20exists%20in%20the%20data.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ShipCountry%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20date_part('year'%2C%20%22OrderDate%22)%20AS%20yr%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_orders%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22ShipCountry%22%20IN%20('France'%2C%20'Belgium')%0A%20%20%20%20%20%20%20%20%20%20AND%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-01-01'%0A%20%20%20%20%20%20%20%20%20%20AND%20%22OrderDate%22%20%3C%20%20DATE%20'2018-01-01'%0A%20%20%20%20%20%20%20%20GROUP%20BY%20%22ShipCountry%22%2C%20yr%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22ShipCountry%22%2C%20yr%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Two%20countries%20and%20two%20years%20give%20four%20rows%2C%20because%20all%20four%20combinations%20occur.%20A%0A%20%20%20%20combination%20that%20never%20happens%20produces%20no%20row%20at%20all%2C%20so%20a%20year%20in%20which%20Belgium%0A%20%20%20%20ordered%20nothing%20would%20be%20absent%20from%20this%20result%2C%20with%20no%20zero%20in%20its%20place.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20group%20%60orders%60%20by%20%60%22ShipCountry%22%60%20and%20%60%22ShipVia%22%60%20and%20count%20the%20rows%20in%0A%20%20%20%20the%20result.%20Work%20out%20what%20the%20number%20would%20be%20if%20every%20combination%20occurred%2C%20compare%2C%0A%20%20%20%20and%20explain%20what%20a%20smaller%20answer%20would%20have%20told%20you%20about%20the%20business.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20groups%20come%20back%20in%20no%20particular%20order%0A%0A%20%20%20%20Chapter%20A%20%C2%A71%20said%20the%20order%20of%20rows%20is%20not%20part%20of%20a%20table%2C%20and%20the%20same%20is%20true%20of%20a%0A%20%20%20%20grouped%20result.%20The%20query%20above%20ends%20in%20%60ORDER%20BY%60%20and%20that%20is%20the%20only%20reason%20the%0A%20%20%20%20countries%20came%20out%20alphabetically.%0A%0A%20%20%20%20Two%20older%20descriptions%20of%20%60GROUP%20BY%60%20are%20worth%20unlearning%2C%20because%20they%20describe%20how%0A%20%20%20%20one%20engine%20used%20to%20implement%20it.%20Grouping%20does%20not%20sort%20its%20output%2C%20and%20it%20does%20not%0A%20%20%20%20merge%20adjacent%20rows.%20It%20produces%20one%20row%20per%20distinct%20combination%2C%20in%20whatever%20order%0A%20%20%20%20the%20engine%20finds%20them.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20country%20query%20with%20the%20%60ORDER%20BY%60%20deleted%2C%20then%20with%0A%20%20%20%20%60ORDER%20BY%20%22ShipCountry%22%60%2C%20then%20with%20%60ORDER%20BY%20n_orders%20DESC%60.%20Same%2021%20rows%2C%20three%0A%20%20%20%20arrangements.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Grouping%20on%20something%20you%20computed%0A%0A%20%20%20%20A%20grouping%20column%20can%20be%20an%20expression.%20%C2%A711's%20%60CASE%60%20band%20and%20%C2%A712's%20%60date_trunc%60%20are%0A%20%20%20%20both%20this%2C%20and%20so%20is%20anything%20else%20you%20write%20in%20%60SELECT%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20CASE%20WHEN%20%22Freight%22%20%3C%20100%20THEN%20'light'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHEN%20%22Freight%22%20%3C%20400%20THEN%20'standard'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ELSE%20'heavy'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20END%20AS%20freight_band%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(avg(%22Freight%22)%2C%202)%20AS%20avg_freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(min(%22Freight%22)%2C%202)%20AS%20lightest%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(max(%22Freight%22)%2C%202)%20AS%20heaviest%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20GROUP%20BY%20freight_band%0A%20%20%20%20%20%20%20%20ORDER%20BY%20n_orders%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20bands%20are%20yours%20and%20the%20counts%20are%20the%20data's.%20Check%20the%20%60min%60%20and%20%60max%60%20inside%0A%20%20%20%20each%20band%20against%20the%20boundaries%20you%20wrote%2C%20which%20is%20how%20you%20catch%20a%20band%20whose%0A%20%20%20%20condition%20does%20not%20say%20what%20you%20meant.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20group%20%60orders%60%20by%20%60date_trunc('year'%2C%20%22OrderDate%22)%60%20and%20count%20them.%20You%0A%20%20%20%20should%20get%2012%20rows%20spanning%20the%20range%20%C2%A712%20measured%2C%20and%20the%20counts%20should%20add%20to%0A%20%20%20%2016%2C282.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20A%20grouped%20result%20has%2021%20rows%20and%20the%20table%20has%2016%2C282.%20What%20has%20to%20be%20true%20about%20the%0A%20%20%20%20%20%20%2021%20counts%3F%0A%20%20%20%202.%20%60SELECT%20%22CategoryID%22%2C%20%22ProductName%22%2C%20count(*)%20...%20GROUP%20BY%20%22CategoryID%22%60%20fails.%20Explain%0A%20%20%20%20%20%20%20in%20one%20sentence%20why%2C%20without%20using%20the%20word%20%22error%22.%0A%20%20%20%203.%20Grouping%20%60orders%60%20by%20country%20and%20year%20gives%20252%20rows%2C%20and%20there%20are%2021%20countries%0A%20%20%20%20%20%20%20and%2012%20years.%20What%20would%20a%20result%20of%20240%20rows%20have%20meant%3F%0A%20%20%20%204.%20Somebody%20says%20%60GROUP%20BY%60%20sorts%20the%20output.%20What%20do%20you%20run%20to%20show%20them%20otherwise%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2015.%20%60HAVING%60%2C%20against%20%60WHERE%60%0A%0A%20%20%20%20Both%20clauses%20filter.%20%60WHERE%60%20filters%20rows%20before%20they%20are%20grouped%20and%20%60HAVING%60%20filters%0A%20%20%20%20groups%20after%20they%20are%20formed.%20Every%20explanation%20of%20the%20difference%20says%20that%20much%3B%20what%0A%20%20%20%20makes%20it%20stick%20is%20the%20query%20you%20cannot%20write.%0A%0A%20%20%20%20Three%20versions%20of%20one%20question%20follow.%20Two%20of%20them%20run%20and%20disagree%2C%20and%20the%20third%20is%0A%20%20%20%20rejected.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Version%201%3A%20%60WHERE%60%20throws%20rows%20away%20first%0A%0A%20%20%20%20How%20many%20products%20does%20each%20category%20have%3F%20Filtering%20to%20the%2069%20current%20products%20first%0A%20%20%20%20means%20the%20discontinued%20ones%20never%20reach%20a%20group.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22CategoryID%22%2C%20count(*)%20AS%20n_products%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20WHERE%20%22Discontinued%22%20%3D%20'0'%0A%20%20%20%20%20%20%20%20GROUP%20BY%20%22CategoryID%22%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22CategoryID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Eight%20groups%2C%20and%20the%20counts%20add%20to%2069.%20%60WHERE%60%20looked%20at%20one%20row%20at%20a%20time%20and%20never%0A%20%20%20%20needed%20to%20know%20what%20a%20group%20was.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Version%202%3A%20%60HAVING%60%20throws%20groups%20away%20last%0A%0A%20%20%20%20Same%20table%2C%20no%20%60WHERE%60%2C%20and%20a%20condition%20on%20the%20group%20instead.%20All%2077%20products%20are%0A%20%20%20%20grouped%2C%20then%20the%20small%20categories%20are%20dropped.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22CategoryID%22%2C%20count(*)%20AS%20n_products%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20GROUP%20BY%20%22CategoryID%22%0A%20%20%20%20%20%20%20%20HAVING%20count(*)%20%3E%208%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22CategoryID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Five%20groups%20instead%20of%20eight.%20Categories%205%2C%206%20and%207%20were%20formed%2C%20counted%2C%20found%20to%20hold%0A%20%20%20%207%2C%206%20and%205%20products%2C%20and%20discarded.%20The%20rows%20in%20them%20were%20read%3B%20the%20groups%20did%20not%0A%20%20%20%20survive.%0A%0A%20%20%20%20%60HAVING%60%20can%20see%20aggregates%20because%20by%20the%20time%20it%20runs%20the%20groups%20exist%20and%20the%0A%20%20%20%20aggregates%20have%20been%20computed.%20That%20is%20the%20whole%20of%20the%20difference.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Version%203%3A%20%60WHERE%20count(*)%20%3E%208%60%20is%20rejected%0A%0A%20%20%20%20The%20same%20condition%2C%20moved%20into%20%60WHERE%60%2C%20does%20not%20run.%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20SELECT%20%22CategoryID%22%2C%20count(*)%20AS%20n_products%0A%20%20%20%20FROM%20products%0A%20%20%20%20WHERE%20count(*)%20%3E%208%0A%20%20%20%20GROUP%20BY%20%22CategoryID%22%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%20Binder%20Error%3A%20WHERE%20clause%20cannot%20contain%20aggregates!%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20Read%20the%20message%20as%20a%20statement%20about%20time.%20When%20%60WHERE%60%20runs%20there%20are%20no%20groups%20yet%2C%0A%20%20%20%20so%20there%20is%20nothing%20for%20%60count(*)%60%20to%20count.%20The%20database%20is%20not%20being%20fussy%20about%0A%20%20%20%20where%20you%20put%20the%20condition%3B%20it%20cannot%20answer%20it%20at%20that%20point%20in%20the%20query.%0A%0A%20%20%20%20That%20error%20is%20the%20reason%20%60HAVING%60%20exists.%20Without%20it%20there%20would%20be%20no%20clause%20that%20runs%0A%20%20%20%20after%20grouping%2C%20and%20no%20way%20to%20ask%20a%20question%20about%20a%20group.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20all%20three%20versions.%20Then%20move%20the%20%60HAVING%60%20condition%20to%20%60WHERE%60%20in%20a%0A%20%20%20%20query%20of%20your%20own%20and%20read%20the%20message%20again%2C%20so%20you%20recognize%20it%20when%20it%20turns%20up%20in%0A%20%20%20%20something%20long.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20What%20each%20version%20asks%0A%0A%20%20%20%20Version%201%20gave%20eight%20groups%20and%20version%202%20gave%20five%2C%20and%20both%20are%20correct.%0A%0A%20%20%20%20-%20%60WHERE%20%22Discontinued%22%20%3D%20'0'%60%20asks%3A%20among%20current%20products%2C%20how%20many%20per%20category%3F%0A%20%20%20%20-%20%60HAVING%20count(*)%20%3E%208%60%20asks%3A%20which%20categories%20are%20large%3F%0A%0A%20%20%20%20Put%20them%20side%20by%20side%20on%20the%20same%20query%20and%20the%20numbers%20change%20again.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22CategoryID%22%2C%20count(*)%20AS%20n_products%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20WHERE%20%22Discontinued%22%20%3D%20'0'%0A%20%20%20%20%20%20%20%20GROUP%20BY%20%22CategoryID%22%0A%20%20%20%20%20%20%20%20HAVING%20count(*)%20%3E%208%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22CategoryID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20same%20five%20categories%2C%20and%20categories%201%20and%202%20have%20dropped%20from%2012%20to%2011%20because%20the%0A%20%20%20%20discontinued%20products%20left%20before%20the%20counting%20started.%20The%20%60HAVING%60%20threshold%20is%20now%0A%20%20%20%20being%20compared%20against%20a%20different%20number%2C%20even%20though%20the%20clause%20is%20identical%20to%0A%20%20%20%20version%202.%0A%0A%20%20%20%20Raise%20the%20threshold%20to%2011%20and%20the%20divergence%20stops%20being%20about%20the%20counts%20and%20starts%0A%20%20%20%20being%20about%20which%20categories%20come%20back%20at%20all.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20'HAVING%20count(*)%20%3E%2011%2C%20all%2077%20products'%20AS%20written%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22CategoryID%22%20FROM%20products%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20%22CategoryID%22%20HAVING%20count(*)%20%3E%2011))%20AS%20groups_returned%0A%20%20%20%20%20%20%20%20UNION%20ALL%0A%20%20%20%20%20%20%20%20SELECT%20'HAVING%20count(*)%20%3E%2011%2C%20current%20products%20only'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22CategoryID%22%20FROM%20products%20WHERE%20%22Discontinued%22%20%3D%20'0'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20%22CategoryID%22%20HAVING%20count(*)%20%3E%2011))%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20first%20form%20keeps%20four%20categories%20and%20the%20second%20keeps%20two.%20The%20%60WHERE%60%20filtered%20no%0A%20%20%20%20group%20directly.%20It%20removed%208%20rows%2C%20and%20that%20pushed%20two%20categories%20under%20a%20threshold%0A%20%20%20%20they%20had%20cleared%20a%20moment%20before.%0A%0A%20%20%20%20**The%20trap.**%20Moving%20a%20condition%20between%20the%20two%20clauses%20can%20change%20the%20answer%20without%0A%20%20%20%20changing%20the%20row%20count%20enough%20to%20notice.%20Decide%20which%20one%20you%20mean%20by%20asking%20whether%0A%20%20%20%20the%20condition%20is%20a%20fact%20about%20a%20row%20or%20a%20fact%20about%20a%20group.%20%60Discontinued%60%20is%20a%20fact%0A%20%20%20%20about%20a%20product.%20%60count(*)%20%3E%208%60%20is%20a%20fact%20about%20a%20category.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20find%20the%20countries%20with%20more%20than%201%2C000%20orders%2C%20then%20the%20countries%20with%0A%20%20%20%20more%20than%201%2C000%20orders%20whose%20freight%20was%20over%20400.%20State%20which%20clause%20each%20condition%20went%0A%20%20%20%20into%20and%20why.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60HAVING%60%20without%20%60GROUP%20BY%60%0A%0A%20%20%20%20A%20query%20with%20no%20%60GROUP%20BY%60%20and%20an%20aggregate%20in%20it%20already%20has%20a%20group%2C%20which%20is%20all%0A%20%20%20%20the%20rows.%20%60HAVING%60%20filters%20that%20one%20group%2C%20so%20the%20result%20is%20either%20the%20row%20or%20nothing.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22SELECT%20count(*)%20AS%20n_products%20FROM%20products%20HAVING%20count(*)%20%3E%208%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22SELECT%20count(*)%20AS%20n_products%20FROM%20products%20HAVING%20count(*)%20%3E%20800%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20first%20returns%2077%20and%20the%20second%20returns%20no%20rows%20at%20all.%20It%20is%20legal%20in%20DuckDB%2C%20and%0A%20%20%20%20it%20is%20rarely%20what%20anybody%20means.%20It%20is%20worth%20recognizing%20because%20a%20%60GROUP%20BY%60%20deleted%0A%20%20%20%20by%20accident%20from%20a%20query%20that%20selects%20only%20aggregates%20turns%20a%20report%20into%20a%20single%0A%20%20%20%20row%20or%20into%20nothing%2C%20with%20no%20error.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20take%20the%20country%20query%20from%20%C2%A714%2C%20delete%20the%20%60GROUP%20BY%60%20line%2C%20add%0A%20%20%20%20%60HAVING%20count(*)%20%3E%20100%60%2C%20and%20predict%20the%20output%20before%20you%20run%20it.%20Then%20take%0A%20%20%20%20%60%22ShipCountry%22%60%20out%20of%20the%20%60SELECT%60%2C%20predict%20again%2C%20and%20run%20it.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20%60WHERE%20count(*)%20%3E%208%60%20is%20rejected.%20Explain%20the%20reason%20in%20terms%20of%20when%20each%20clause%0A%20%20%20%20%20%20%20runs%2C%20without%20answering%20%22because%20that%20is%20the%20rule%22.%0A%20%20%20%202.%20Version%201%20gives%20eight%20rows%20and%20version%202%20gives%20five.%20Neither%20is%20wrong.%20What%20are%20the%0A%20%20%20%20%20%20%20two%20questions%3F%0A%20%20%20%203.%20You%20want%20the%20categories%20with%20more%20than%208%20current%20products.%20Which%20clause%20holds%0A%20%20%20%20%20%20%20%60Discontinued%60%2C%20which%20holds%20the%20count%2C%20and%20why%20can%20neither%20move%3F%0A%20%20%20%204.%20A%20query%20returns%20one%20row%20where%20you%20expected%2020.%20What%20is%20the%20first%20line%20to%20look%20for%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2016.%20The%20order%20the%20clauses%20actually%20run%20in%0A%0A%20%20%20%20You%20write%20a%20query%20starting%20at%20%60SELECT%60.%20The%20database%20runs%20it%20starting%20at%20%60FROM%60.%20Every%0A%20%20%20%20rule%20in%20this%20chapter%20that%20felt%20arbitrary%20follows%20from%20that%20one%20gap%2C%20and%20once%20you%20have%0A%20%20%20%20the%20run%20order%20you%20can%20stop%20memorizing%20them.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Building%20and%20querying%20your%20database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Written%20order%20and%20run%20order%0A%0A%20%20%20%20%7C%20you%20write%20%7C%20it%20runs%20%7C%0A%20%20%20%20%7C---%7C---%7C%0A%20%20%20%20%7C%20%60SELECT%60%20%7C%201.%20%60FROM%60%20%7C%0A%20%20%20%20%7C%20%60FROM%60%20%7C%202.%20%60WHERE%60%20%7C%0A%20%20%20%20%7C%20%60WHERE%60%20%7C%203.%20%60GROUP%20BY%60%20%7C%0A%20%20%20%20%7C%20%60GROUP%20BY%60%20%7C%204.%20%60HAVING%60%20%7C%0A%20%20%20%20%7C%20%60HAVING%60%20%7C%205.%20%60SELECT%60%20%7C%0A%20%20%20%20%7C%20%60ORDER%20BY%60%20%7C%206.%20%60ORDER%20BY%60%20%7C%0A%20%20%20%20%7C%20%60LIMIT%60%20%7C%207.%20%60LIMIT%60%20%7C%0A%0A%20%20%20%20Read%20the%20right%20column%20down%3A%20get%20the%20rows%2C%20throw%20some%20away%2C%20put%20the%20rest%20into%20groups%2C%0A%20%20%20%20throw%20some%20groups%20away%2C%20work%20out%20the%20columns%2C%20sort%20what%20is%20left%2C%20and%20cut%20it%20short.%0A%0A%20%20%20%20Only%20%60SELECT%60%20moved.%20Take%20it%20from%20the%20top%20of%20the%20left%20column%2C%20put%20it%20after%20%60HAVING%60%2C%0A%20%20%20%20and%20you%20have%20the%20right%20column.%20%60SELECT%60%20runs%20fifth%20because%20the%20expressions%20in%20it%20are%0A%20%20%20%20computed%20on%20groups%20that%20do%20not%20exist%20until%20%60GROUP%20BY%60%20has%20run%2C%20and%20with%20%60SELECT%60%20gone%0A%20%20%20%20from%20the%20top%2C%20%60FROM%60%20comes%20first%2C%20because%20there%20is%20nothing%20to%20do%20until%20there%20are%20rows.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20write%20out%20the%20run%20order%20from%20memory%2C%20then%20check%20it%20against%20the%20table.%0A%20%20%20%20State%2C%20for%20each%20of%20the%20seven%2C%20what%20has%20to%20have%20happened%20before%20it%20can%20start.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Why%20%60WHERE%60%20cannot%20filter%20on%20a%20count%0A%0A%20%20%20%20%C2%A715's%20rejected%20query%20is%20the%20proof.%20The%20run%20order%20on%20this%20page%20is%20what%20its%20error%0A%20%20%20%20message%20was%20reporting.%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20SELECT%20%22CategoryID%22%2C%20count(*)%20FROM%20products%20WHERE%20count(*)%20%3E%208%20GROUP%20BY%20%22CategoryID%22%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%20Binder%20Error%3A%20WHERE%20clause%20cannot%20contain%20aggregates!%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%60WHERE%60%20is%20step%202%20and%20%60GROUP%20BY%60%20is%20step%203.%20At%20step%202%20there%20are%20no%20groups%2C%20so%20%60count(*)%60%0A%20%20%20%20has%20nothing%20to%20count.%20The%20engine%20is%20not%20refusing%20a%20badly%20placed%20condition%3B%20it%20is%0A%20%20%20%20refusing%20one%20that%20cannot%20be%20evaluated%20yet.%0A%0A%20%20%20%20%60HAVING%60%20is%20step%204%20and%20sees%20aggregates%20for%20the%20same%20reason%20in%20reverse.%20Everything%20in%0A%20%20%20%20%C2%A715%20is%20this%20table.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20predict%20which%20of%20these%20four%20run%20and%20which%20are%20rejected%2C%20then%20check.%0A%20%20%20%20%60WHERE%20avg(%22UnitPrice%22)%20%3E%2020%60%3B%20%60HAVING%20avg(%22UnitPrice%22)%20%3E%2020%60%3B%0A%20%20%20%20%60WHERE%20%22UnitPrice%22%20%3E%2020%60%3B%20%60HAVING%20%22CategoryID%22%20%3E%204%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Sorting%20by%20an%20alias%0A%0A%20%20%20%20%C2%A78%20sorted%20by%20%60doubled%60%2C%20a%20name%20that%20exists%20nowhere%20in%20%60products%60.%20It%20works%20because%0A%20%20%20%20%60SELECT%60%20is%20step%205%20and%20%60ORDER%20BY%60%20is%20step%206%2C%20so%20by%20the%20time%20the%20sort%20runs%20the%20column%0A%20%20%20%20has%20been%20computed%20and%20named.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ShipCountry%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(avg(%22Freight%22)%2C%202)%20AS%20avg_freight%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20GROUP%20BY%20%22ShipCountry%22%0A%20%20%20%20%20%20%20%20HAVING%20count(*)%20%3E%20500%0A%20%20%20%20%20%20%20%20ORDER%20BY%20avg_freight%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%205%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60ORDER%20BY%20avg_freight%60%20sorts%20on%20a%20name%20that%20%60SELECT%60%20invented%20two%20lines%20earlier.%20That%0A%20%20%20%20is%20the%20whole%20statement%2C%20in%20run%20order%3A%20read%20%60orders%60%2C%20group%20by%20country%2C%20drop%20the%0A%20%20%20%20countries%20under%20500%20orders%2C%20compute%20the%20two%20aggregate%20columns%2C%20sort%20by%20one%20of%20them%2C%20and%0A%20%20%20%20keep%20the%20top%205.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20take%20that%20query%20and%20replace%20%60ORDER%20BY%20avg_freight%60%20with%0A%20%20%20%20%60ORDER%20BY%20round(avg(%22Freight%22)%2C%202)%60.%20Confirm%20you%20get%20the%20same%20five%20rows%2C%20then%20state%20which%0A%20%20%20%20version%20you%20would%20rather%20read%20in%20six%20months.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20An%20alias%20in%20%60WHERE%60%0A%0A%20%20%20%20The%20textbook%20demonstration%20of%20the%20run%20order%20is%20that%20a%20%60SELECT%60%20alias%20cannot%20be%20used%20in%0A%20%20%20%20%60WHERE%60%2C%20because%20%60WHERE%60%20runs%20first.%20DuckDB%20accepts%20it%20anyway.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20%22ProductName%22%2C%20round(%22UnitPrice%22%20*%202%2C%202)%20AS%20doubled%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20WHERE%20doubled%20%3E%20100%0A%20%20%20%20%20%20%20%20ORDER%20BY%20doubled%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Seven%20rows%20come%20back%20and%20nothing%20complains.%20DuckDB%20resolves%20the%20name%20against%20the%0A%20%20%20%20%60SELECT%60%20list%20as%20a%20convenience%20and%20substitutes%20the%20expression%2C%20so%20the%20query%20it%20actually%20runs%20is%0A%20%20%20%20%60WHERE%20round(%22UnitPrice%22%20*%202%2C%202)%20%3E%20100%60.%0A%0A%20%20%20%20**The%20trap.**%20That%20is%20a%20DuckDB%20feature%2C%20and%20the%20run%20order%20is%20what%20the%20standard%20says%2C%20so%0A%20%20%20%20an%20engine%20that%20follows%20the%20standard%20answers%20this%20query%20with%20an%20unknown-column%20error.%0A%20%20%20%20Repeat%20the%20expression%20in%20%60WHERE%60%2C%20or%20wrap%20the%20query%20in%20another%20one%20and%20filter%20outside%2C%0A%20%20%20%20which%20is%20Chapter%20C%20%C2%A721.%0A%0A%20%20%20%20The%20run%20order%20is%20still%20the%20truth.%20What%20changed%20is%20that%20DuckDB%20rewrites%20your%20query%0A%20%20%20%20before%20running%20it%2C%20and%20a%20convenience%20that%20only%20exists%20in%20one%20engine%20is%20a%20poor%20thing%20to%0A%20%20%20%20learn%20the%20rule%20from.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20query%20above%2C%20then%20run%20it%20again%20with%20%60WHERE%20doubled%20%3E%20100%60%0A%20%20%20%20replaced%20by%20%60HAVING%20doubled%20%3E%20100%60%20and%20read%20what%20happens.%20Then%20ask%20an%20agent%20whether%0A%20%20%20%20your%20own%20database%20engine%20accepts%20an%20alias%20in%20%60WHERE%60%2C%20and%20test%20whatever%20it%20tells%20you.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60LIMIT%60%20runs%20last%0A%0A%20%20%20%20%60LIMIT%205%60%20on%20a%20grouped%20and%20sorted%20query%20gives%20the%20top%20five%20groups.%20It%20does%20not%20read%0A%20%20%20%20five%20rows%20and%20then%20group%20them.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20date_trunc('year'%2C%20%22OrderDate%22)%20AS%20yr%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20sum(CASE%20WHEN%20%22ShippedDate%22%20%3E%20%22RequiredDate%22%20THEN%201%20ELSE%200%20END)%20AS%20late%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(100.0%20*%20sum(CASE%20WHEN%20%22ShippedDate%22%20%3E%20%22RequiredDate%22%20THEN%201%20ELSE%200%20END)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%20count(*)%2C%201)%20AS%20pct_late%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-01-01'%20AND%20%22OrderDate%22%20%3C%20DATE%20'2019-01-01'%0A%20%20%20%20%20%20%20%20GROUP%20BY%20yr%0A%20%20%20%20%20%20%20%20HAVING%20count(*)%20%3E%201000%0A%20%20%20%20%20%20%20%20ORDER%20BY%20pct_late%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%203%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Every%20clause%20in%20this%20chapter%20is%20in%20that%20query.%20In%20run%20order%3A%20read%20%60orders%60%2C%20keep%20the%0A%20%20%20%20three%20years%202016%20to%202018%2C%20group%20them%20by%20year%2C%20drop%20any%20year%20under%201%2C000%20orders%2C%20compute%0A%20%20%20%20the%20count%20and%20the%20two%20%60CASE%60%20columns%2C%20sort%20by%20the%20percentage%2C%20and%20take%20three.%0A%0A%20%20%20%20The%20worst%20of%20the%20three%20years%20was%202016%20at%2024.6%20percent%20late%2C%20and%20the%20best%20was%202017%20at%0A%20%20%20%2019.8.%20The%0A%20%20%20%20%60WHERE%60%20uses%20the%20half-open%20form%20from%20%C2%A712%2C%20the%20%60CASE%60%20counts%20a%20condition%20the%20way%20%C2%A711%0A%20%20%20%20does%2C%20the%20%60HAVING%60%20is%20a%20fact%20about%20a%20group%20and%20the%20%60pct_late%60%20in%20%60ORDER%20BY%60%20is%20an%20alias%0A%20%20%20%20that%20exists%20because%20%60SELECT%60%20ran%20first.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20delete%20one%20clause%20at%20a%20time%20from%20that%20query%20and%20predict%20what%20each%0A%20%20%20%20deletion%20does%20before%20you%20run%20it.%20Start%20with%20%60HAVING%60%2C%20then%20%60LIMIT%60%2C%20then%20%60ORDER%20BY%60%2C%0A%20%20%20%20then%20%60WHERE%60.%0A%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20Name%20the%20clause%20you%20move%20to%20turn%20the%20written%20order%20into%20the%20run%20order%2C%20and%0A%20%20%20%20%20%20%20explain%20what%20its%20move%20makes%20possible.%0A%20%20%20%202.%20%60WHERE%20count(*)%20%3E%208%60%20and%20%60HAVING%20count(*)%20%3E%208%60.%20One%20is%20rejected.%20Answer%20using%20step%0A%20%20%20%20%20%20%20numbers%2C%20and%20without%20the%20word%20%22rule%22.%0A%20%20%20%203.%20%60ORDER%20BY%20avg_freight%60%20works%20on%20an%20alias.%20Which%20step%20made%20that%20name%20exist%2C%20and%20which%0A%20%20%20%20%20%20%20step%20used%20it%3F%0A%20%20%20%204.%20A%20colleague's%20query%20works%20in%20DuckDB%20and%20fails%20on%20another%20engine%20with%20%22column%20doubled%0A%20%20%20%20%20%20%20does%20not%20exist%22.%20What%20did%20they%20write%2C%20and%20what%20is%20the%20fix%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%20Chapter%20challenge%0A%0A%20%20%20%20Nothing%20here%20is%20due.%20Each%20section%20above%20teaches%20one%20clause%2C%20and%20a%20real%20question%20needs%0A%20%20%20%20most%20of%20them%20at%20once.%0A%0A%20%20%20%20**Answer%20this%2C%20from%20%60orders%60%20alone%3A%20how%20late%20are%20shipments%2C%20and%20is%20it%20getting%20worse%3F**%0A%0A%20%20%20%20%C2%A711%20already%20has%20the%20headline.%20Of%2016%2C282%20orders%2C%203%2C755%20shipped%20after%20the%20date%20they%20were%0A%20%20%20%20required%2C%20which%20is%2023.1%20percent%2C%20and%20the%20yearly%20figure%20has%20moved%20between%2019.8%20and%2025.1%0A%20%20%20%20percent%20across%2012%20years.%20Those%20two%20sentences%20are%20the%20starting%20point.%20Neither%20of%20them%0A%20%20%20%20says%20whether%20the%20trend%20is%20real.%0A%0A%20%20%20%20**Done%20looks%20like**%20one%20query%20per%20claim%2C%20each%20with%20its%20output%20visible%2C%20and%20one%20page%20of%0A%20%20%20%20prose.%20The%20prose%20states%20what%20you%20found%2C%20names%20the%20three%20decisions%20below%20and%20says%20which%0A%20%20%20%20way%20you%20went%20on%20each%2C%20and%20carries%20a%20**How%20I%20know%20these%20numbers%20are%20right**%20section%20with%0A%20%20%20%20at%20least%20one%20independent%20check.%20A%20check%20is%20a%20second%20query%20that%20would%20disagree%20if%20the%0A%20%20%20%20first%20one%20were%20wrong.%20%60count(*)%60%20against%20the%20sum%20of%20your%20groups%20is%20the%20cheapest%20one%20and%0A%20%20%20%20it%20is%20not%20the%20only%20one%20you%20need.%0A%0A%20%20%20%20**Three%20decisions%20are%20yours%2C%20and%20no%20query%20will%20make%20them.**%0A%0A%20%20%20%201.%20**What%20counts%20as%20late.**%20%60%22ShippedDate%22%20%3E%20%22RequiredDate%22%60%20is%20one%20answer.%20Shipped%20more%0A%20%20%20%20%20%20%20than%20seven%20days%20after%20the%20order%20is%20another%2C%20and%20it%20measures%20something%20different.%0A%20%20%20%20%20%20%20Write%20down%20which%20question%20your%20definition%20answers.%0A%20%20%20%202.%20**What%20you%20do%20with%20the%2021.**%20They%20were%20placed%20and%20never%20shipped.%20Late%2C%20excluded%2C%20or%0A%20%20%20%20%20%20%20their%20own%20category%2C%20and%20each%20choice%20moves%20the%20percentage%20by%20a%20different%20amount.%20State%0A%20%20%20%20%20%20%20which%20and%20by%20how%20much.%0A%20%20%20%203.%20**What%20a%20year%20is.**%20%60date_part('year'%2C%20%22OrderDate%22)%60%20groups%20by%20when%20the%20order%20was%0A%20%20%20%20%20%20%20placed.%20Grouping%20by%20%60%22ShippedDate%22%60%20puts%20an%20order%20placed%20in%20December%20and%20shipped%20in%0A%20%20%20%20%20%20%20January%20into%20the%20next%20year.%20Both%20are%20defensible%20and%20they%20give%20different%20trends.%0A%0A%20%20%20%20**Going%20further.**%20Break%20the%20number%20down%20by%20something%20that%20could%20explain%20it%3A%20the%0A%20%20%20%20shipper%2C%20the%20destination%20country%2C%20the%20order%20size%2C%20the%20month%20of%20the%20year.%20If%20one%0A%20%20%20%20breakdown%20moves%20the%20picture%2C%20get%20the%20same%20number%20two%20ways%20and%20see%20whether%20it%20survives.%0A%20%20%20%20Then%20look%20at%20whether%20the%20830%20midnight%20orders%20behave%20differently%20from%20the%20other%2015%2C452%2C%0A%20%20%20%20because%20a%20block%20of%20rows%20that%20came%20from%20a%20different%20source%20is%20the%20sort%20of%20thing%20that%0A%20%20%20%20produces%20a%20trend%20nobody%20in%20the%20business%20would%20recognize.%20Write%20the%20sentence%20you%20would%0A%20%20%20%20put%20in%20front%20of%20an%20operations%20manager%2C%20and%20then%20write%20the%20one%20sentence%20they%20would%20use%20to%20push%0A%20%20%20%20back%20on%20you.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%20%F0%9F%A4%96%20Ask%20your%20agent%0A%0A%20%20%20%20Two%20different%20uses%2C%20and%20the%20second%20one%20is%20the%20one%20this%20course%20grades.%0A%0A%20%20%20%20%23%23%23%20Ask%20it%20about%20what%20this%20chapter%20left%20out%0A%0A%20%20%20%20-%20This%20chapter%20never%20used%20%60EXISTS%60%2C%20%60ANY%60%20or%20%60ALL%60.%20Ask%20what%20each%20of%20them%20does%20and%20why%0A%20%20%20%20%20%20a%20%60WHERE%20...%20IN%20(...)%60%20with%20a%20list%20of%20values%20is%20a%20different%20thing%20from%0A%20%20%20%20%20%20%60WHERE%20...%20IN%20(SELECT%20...)%60.%0A%20%20%20%20-%20Ask%20what%20%60QUALIFY%60%20does%20in%20DuckDB%2C%20and%20how%20it%20relates%20to%20the%20run%20order%20in%20%C2%A716.%0A%20%20%20%20-%20Ask%20why%20%60NULL%60%20behaves%20the%20way%20it%20does%2C%20using%20the%20phrase%20%22three-valued%20logic%22.%20Then%0A%20%20%20%20%20%20ask%20it%20what%20%60WHERE%20NOT%20(%22Country%22%20%3D%20'USA')%60%20does%20to%20rows%20where%20%60Country%60%20is%20%60NULL%60.%0A%20%20%20%20-%20Ask%20which%20of%20%60date_trunc%60%2C%20%60date_part%60%2C%20%60date_diff%60%20and%20%60strftime%60%20exist%20in%20SQL%0A%20%20%20%20%20%20Server%2C%20PostgreSQL%20and%20SQLite%2C%20and%20what%20each%20is%20called%20there.%20Check%20two%20of%20its%0A%20%20%20%20%20%20answers%20against%20those%20engines'%20own%20documentation.%0A%20%20%20%20-%20Ask%20what%20an%20index%20is%2C%20and%20why%20%60WHERE%20date_part('year'%2C%20%22OrderDate%22)%20%3D%202016%60%20cannot%0A%20%20%20%20%20%20use%20one%20while%20%60WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-01-01'%20AND%20%22OrderDate%22%20%3C%20DATE%0A%20%20%20%20%20%20'2017-01-01'%60%20can.%0A%0A%20%20%20%20Test%20whatever%20comes%20back.%20Run%20it.%0A%0A%20%20%20%20%23%23%23%20Watch%20what%20it%20decides%20without%20telling%20you%0A%0A%20%20%20%20Give%20it%20this%2C%20exactly%3A%0A%0A%20%20%20%20%3E%20*%22Write%20me%20a%20SQL%20query%20showing%20monthly%20order%20volume%20from%20the%20Northwind%20orders%20table.%22*%0A%0A%20%20%20%20Then%20read%20what%20came%20back%20and%20answer%20five%20questions.%0A%0A%20%20%20%201.%20**Did%20it%20write%20%60BETWEEN%60%20or%20a%20half-open%20range%3F**%20If%20there%20is%20no%20date%20filter%20at%20all%2C%0A%20%20%20%20%20%20%20that%20is%20also%20a%20decision.%0A%20%20%20%202.%20**Did%20it%20group%20on%20the%20raw%20%60OrderDate%60%20or%20truncate%20it%20first%3F**%20Run%20it.%20A%20result%20with%0A%20%20%20%20%20%20%2015%2C932%20rows%20in%20it%20is%20the%20%C2%A712%20failure.%0A%20%20%20%203.%20**What%20did%20it%20assume%20the%20columns%20are%20called%3F**%20Northwind%20exists%20in%20a%20dozen%20versions.%0A%20%20%20%20%20%20%20Check%20every%20column%20name%20it%20used%20against%20%60DESCRIBE%20orders%60.%0A%20%20%20%204.%20**What%20years%20did%20it%20write%20into%20the%20query%3F**%20Anything%20from%201996%20to%201998%20came%20from%20its%0A%20%20%20%20%20%20%20training%20data%20and%20returns%20zero%20rows%20here.%0A%20%20%20%205.%20**Did%20it%20handle%20the%20rows%20where%20%60ShippedDate%60%20is%20%60NULL%60%3F**%20It%20was%20not%20asked%20to%2C%20and%0A%20%20%20%20%20%20%20whether%20it%20did%20tells%20you%20something%20about%20what%20else%20it%20did%20without%20being%20asked.%0A%0A%20%20%20%20You%20asked%20for%20one%20query%20and%20got%20half%20a%20dozen%20decisions%2C%20and%20each%20one%20is%20a%20claim%20about%0A%20%20%20%20data%20it%20has%20never%20seen.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20send%20the%20same%20sentence%20twice%2C%20in%20two%20separate%20conversations%2C%20and%20diff%20the%0A%20%20%20%20two%20queries.%20Then%20send%20it%20a%20third%20time%20with%20one%20extra%20sentence%20saying%20the%20date%20columns%0A%20%20%20%20are%20timestamps%20and%20the%20data%20runs%20from%202012%20to%202023%2C%20and%20see%20which%20of%20its%20assumptions%0A%20%20%20%20survive.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%20Chapter%20B%20checklist%0A%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20write%20a%20%60SELECT%60%20that%20names%20its%20columns%20and%20quote%20a%20name%20with%20a%20space%20in%20it%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20write%20a%20%60WHERE%60%20with%20%60AND%60%20and%20%60OR%60%20in%20it%20and%20explain%20why%20the%20parentheses%20are%0A%20%20%20%20%20%20%20%20%20%20there%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20explain%20what%20%60LIMIT%60%20means%20when%20there%20is%20no%20%60ORDER%20BY%60%20above%20it%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20tell%20%60IS%20NULL%60%20from%20%60%3D%20NULL%60%20and%20state%20what%20the%20second%20one%20returns%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20explain%20why%20a%20filter%20and%20its%20opposite%20do%20not%20add%20up%20to%20the%20whole%20table%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20explain%20what%20%60DISTINCT%60%20does%20to%20%60NULL%60%20and%20why%20%60count(DISTINCT%20x)%60%20disagrees%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20order%20the%20branches%20of%20a%20%60CASE%60%20so%20that%20none%20of%20them%20is%20unreachable%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20count%20a%20condition%20with%20%60sum(CASE%20WHEN%20...%20THEN%201%20ELSE%200%20END)%60%20and%20state%20where%0A%20%20%20%20%20%20%20%20%20%20the%20missing%20values%20went%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20write%20a%20date%20range%20that%20does%20not%20lose%20the%20last%20day%20of%20the%20month%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20explain%20what%20%60date_diff('day'%2C%20...)%60%20is%20counting%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20tell%20%60count(*)%60%20from%20%60count(column)%60%20and%20explain%20what%20the%20gap%20between%20them%20is%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20group%20a%20table%20and%20check%20that%20the%20group%20counts%20add%20back%20up%20to%20the%20row%20count%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20decide%20whether%20a%20condition%20belongs%20in%20%60WHERE%60%20or%20in%20%60HAVING%60%20and%20explain%20why%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20read%20the%20run%20order%20off%20the%20seven%20clauses%20and%20use%20it%20to%20explain%20an%20error%0A%20%20%20%20%20%20%20%20%20%20message%0A%0A%20%20%20%20**Part%20C%20is%20more%20than%20one%20table.**%20It%20covers%20%60JOIN%60%2C%20what%20a%20%60LEFT%20JOIN%60%20preserves%2C%20and%0A%20%20%20%20the%20case%20where%20joining%20two%20tables%20gives%20you%20more%20rows%20than%20either%20of%20them%20started%20with.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0Aif%20__name__%20%3D%3D%20%22__main__%22%3A%0A%20%20%20%20app.run()%0A
7e11ad9dceeedefda7c862e83470ddbc