# /// script
# requires-python = ">=3.12,<3.15"
# dependencies = [
#     "marimo[sql]",
#     "duckdb",
# ]
# ///
"""SQL Handbook, Chapter B: Asking One Table a Question.
"""

import marimo

__generated_with = "0.24.0"
app = marimo.App(width="medium", sql_output="polars")


@app.cell
def _():
    import duckdb
    import marimo as mo

    return duckdb, mo


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    # Asking One Table a Question

    Chapter B of the SQL Handbook. There are 11 sections, each one concept, and together
    they are one statement.

    Chapter A gave you the vocabulary. This chapter is the query. By §16 you will have
    written every clause of the longest form a single-table query takes, and you will
    know which of them runs first.

    Every query below runs against the course warehouse, Northwind, and every one of them
    shows the rows it returned. You can read this straight through, or open it when you
    need one thing. Each section ends with questions that have no answers written down,
    because those are the ones asked out loud in class.

    To run the code yourself, download the `.py` into your project and open it with
    `uv run marimo edit`.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Opening the warehouse

    The cell below loads the 13 parquet files in `data/northwind/` into DuckDB as 13
    tables. How that works is Part D, sections 26 and 27, and nothing in this chapter
    depends on reading it.

    One name matters from the first query. The file is `order-details.parquet`, the
    table is `"Order Details"` with a space in it, and every mention of it needs double
    quotes.
    """)
    return


@app.cell
def _(duckdb, mo):
    # Look for a data/northwind folder here and in every folder above, so the
    # chapter works wherever you save it. If there is none, read the copy on the
    # course site instead.
    _here = mo.notebook_dir()
    _source = next(
        (p / "data" / "northwind" for p in [_here, *_here.parents]
         if (p / "data" / "northwind").is_dir()),
        "https://oim7510.github.io/data/northwind",
    )

    con = duckdb.connect()

    _files = {
        "categories": "categories",
        "customercustomerdemo": "customercustomerdemo",
        "customerdemographics": "customerdemographics",
        "customers": "customers",
        "employees": "employees",
        "employeeterritories": "employeeterritories",
        "Order Details": "order-details",
        "orders": "orders",
        "products": "products",
        "regions": "regions",
        "shippers": "shippers",
        "suppliers": "suppliers",
        "territories": "territories",
    }
    for _table, _file in _files.items():
        con.execute(
            f"""CREATE TABLE "{_table}" AS SELECT * FROM '{_source}/{_file}.parquet'"""
        )
    return (con,)


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 6. `SELECT` and `FROM`

    A query is a sentence with a fixed vocabulary. The two words you cannot leave out are
    `SELECT`, which names the columns you want back, and `FROM`, which names the table
    they come from. Everything else in this chapter narrows, sorts, groups or counts what
    those two give you.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `SELECT` names columns and `FROM` names the table

    `products` has 10 columns and 77 rows. This asks for three of the columns and, for
    now, five of the rows.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductID", "ProductName", "UnitPrice"
        FROM products
        LIMIT 5
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The result is a table. It has the columns you asked for, in the order you asked for
    them, and it is not stored anywhere. The database built it when you pressed run and
    will build it again next time.

    Two habits are worth starting with. Put each **clause**, a keyword and what follows
    it, on its own line, because a query grows and a one-line query stops being readable
    at about the third clause. And write the keywords in capitals, which nothing enforces
    and every SQL reader expects.

    👉 **Try it:** ask `customers` for `"CustomerID"`, `"CompanyName"` and `"Country"`,
    five rows. Then swap the order of the three column names and run it again.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `SELECT *` gives you every column

    The star means all of them, and it is the fastest way to see what a table holds.
    """)
    return


@app.cell
def _(con):
    con.sql("SELECT * FROM orders LIMIT 2")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Read the line under the result: `2 rows`, `14 columns (7 shown)`. The display gave up
    and hid seven columns behind the `…`, which is what a wide table does to a screen.

    **The trap.** `SELECT *` in anything you keep is a query whose meaning changes when
    somebody adds a column to the table. It is a good way to look and a poor way to
    write. Name the columns in anything you save, share or build on.

    `DESCRIBE` is the better way to find out what is in there, because it gives you one
    row per column instead of one column per column.

    👉 **Try it:** run `DESCRIBE orders`, then write a `SELECT` naming the four columns
    you would actually want if somebody asked you which orders went to France.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A name with a space in it needs double quotes

    DuckDB folds an unquoted name to lower case and matches it without regard to case, so
    `SELECT orderid FROM orders` finds the column called `OrderID`. That works until a
    name contains something the parser cannot read as a single word.
    """)
    return


@app.cell
def _(con):
    con.sql("""SELECT orderid, shipcountry FROM orders LIMIT 2""")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The order-lines table is called `Order Details`, with a space. Unquoted, the parser
    reads `Order` as the whole name and then finds a stray word after it:

    ```sql
    SELECT * FROM Order Details LIMIT 2
    ```

    ```
    Parser Error: syntax error at or near "Order"

    LINE 1: SELECT * FROM Order Details LIMIT 2
                          ^
    ```

    Double quotes make the whole thing one name.
    """)
    return


@app.cell
def _(con):
    con.sql("""SELECT "OrderID", "ProductID", "Quantity" FROM "Order Details" LIMIT 3""")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    **Double quotes are for names. Single quotes are for text.** `'France'` is a value you
    are comparing against and `"ShipCountry"` is the column you are comparing. Swapping
    them gives you either a column that does not exist or a comparison against the literal
    word.

    This handbook quotes every column name, which is more punctuation than DuckDB needs.
    Engines disagree about what an unquoted name means, and a quoted one means the same
    thing everywhere, so the extra keystrokes are what makes a query travel.

    👉 **Try it:** run `SELECT count(*) FROM "Order Details"`, then delete the double
    quotes and read the message you get.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A column can be one you compute

    Nothing says a `SELECT` item has to be a column that exists. An expression is a
    column too, and it is computed once per row.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductName",
               "UnitsInStock",
               "ReorderLevel",
               "UnitsInStock" - "ReorderLevel" AS above_reorder
        FROM products
        LIMIT 5
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `AS above_reorder` gives the computed column a name, an **alias**. Without one, DuckDB
    names it after the expression, and a column called `("UnitsInStock" - "ReorderLevel")`
    is awkward to refer to and worse to read.

    Two rows there are negative, which means those products are below the level at which
    somebody meant to reorder them. Nothing in the table said so; the subtraction said so.

    An alias that needs a space or a capital gets double quotes, for the same reason a
    table name does.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductName" AS "Product name",
               round("UnitPrice", 2) AS "Price"
        FROM products
        LIMIT 3
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `round(..., 2)` is doing real work there. `UnitPrice` is stored as `DECIMAL(12,4)` and
    prints as `18.0000`, so a price quoted as `18.00` in a memo came from somewhere and it
    is worth knowing where.

    👉 **Try it:** add a column to that query holding `"UnitPrice" * "UnitsInStock"`,
    round it to two decimals, and name it `stock_value`. Then state in one sentence what
    that number means for the business.

    ### ✔ Check your understanding

    1. You run `SELECT * FROM orders` and the result says `14 columns (7 shown)`. What
       does that tell you about the table, and what does it tell you about the query?
    2. A colleague's saved report starts `SELECT * FROM products`. Somebody adds a column
       to `products` on Monday. What happens to the report?
    3. When does a column name need double quotes, and when does a value need single
       quotes?
    4. `"UnitsInStock" - "ReorderLevel"` returns a negative number for two of the five
       rows above. Is that a data problem?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 7. `WHERE`

    `SELECT` chooses columns and `WHERE` chooses rows. It takes a condition, called a
    **predicate**, tests it once per row, and keeps the rows where the answer is true.
    Most of the mistakes in this chapter are made in this clause, because a condition that
    is wrong still runs.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A condition tested on every row

    Two of the 77 products cost more than 100.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductID", "ProductName", round("UnitPrice", 2) AS price
        FROM products
        WHERE "UnitPrice" > 100
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The comparison operators are `=`, `<>`, `<`, `<=`, `>` and `>=`. SQL writes "not equal
    to" as `<>`; `!=` also works in DuckDB and in most engines, and `<>` is the one that
    works everywhere.

    Note what the query did not need. There is no `WHERE "UnitPrice" IS A NUMBER`, because
    Chapter A §1 already established that the column has one type and every row obeys it.

    👉 **Try it:** find the products with fewer than 10 units in stock. Then find the ones
    with none at all, and state whether those two questions have the same answer.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Quoting text and doubling an apostrophe

    Six customers have an apostrophe in their name. `Bon app'` is one of them, and typing
    it into a query is the first place the difference between quoting a name and quoting a
    value gets sharp.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "CustomerID", "CompanyName", "City", "Country"
        FROM customers
        WHERE "CompanyName" = 'Bon app'''
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    That is three single quotes at the end. Two of them are the apostrophe in the name
    and the third closes the string. SQL escapes a single quote by doubling it, and
    nothing else. A backslash does not do it.

    **The trap.** An unescaped apostrophe does not give you the wrong rows. It ends the
    string early and leaves the rest of the name sitting in the query as syntax, so you
    get a parser error whose message points at a piece of a company name. When that
    happens, count the quotes before you read anything else.

    Nine product names carry one too, including `Chef Anton's Cajun Seasoning` and
    `Sirop d'érable`.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductID", "ProductName"
        FROM products
        WHERE "ProductName" LIKE '%''%'
        ORDER BY "ProductID"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    👉 **Try it:** write the `WHERE` that finds the customer called `B's Beverages`, by
    exact match. Then try it once with a single apostrophe and read the error.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Check the type before you write the comparison

    `products.Discontinued` holds a 0 or a 1 and looks like a number. `DESCRIBE` says
    otherwise.
    """)
    return


@app.cell
def _(con):
    con.sql("""SELECT * FROM (DESCRIBE products) WHERE column_name = 'Discontinued'""")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    It is a `VARCHAR`, which is the column's **data type**, so the value is the text
    `'0'`, and the right comparison is `"Discontinued" = '0'`. DuckDB is forgiving here
    and will **cast** for you, converting the text to a number, so `= 0` also returns 69
    rows. Comparing a text column to a number is the kind of line that depends on the
    engine being generous, and `DESCRIBE` costs one query.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "Discontinued", count(*) AS n_products
        FROM products
        GROUP BY "Discontinued"
        ORDER BY "Discontinued"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    That is 69 current products and 8 discontinued ones. This chapter writes `= '0'`
    everywhere.

    👉 **Try it:** run `DESCRIBE orders` and find every column whose type would surprise
    you if you guessed from the name alone. `Freight` and the three date columns are the
    ones to look at.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `AND` binds tighter than `OR`

    Two conditions joined with `AND` keep the rows where both hold. Joined with `OR`, they
    keep the rows where either one holds.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*) AS france_and_heavy
        FROM orders
        WHERE "ShipCountry" = 'France' AND "Freight" > 100
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Mixing the two is where it goes wrong. Read these two out loud and they sound
    identical. They differ by one pair of parentheses.

    ```sql
    WHERE "ShipCountry" = 'France' OR "ShipCountry" = 'Belgium' AND "Freight" > 500
    WHERE ("ShipCountry" = 'France' OR "ShipCountry" = 'Belgium') AND "Freight" > 500
    ```

    The query below runs both and puts the two answers in one result with `UNION ALL`, a
    **set operator** that stacks one result on top of another.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'no parentheses' AS written,
               (SELECT count(*) FROM orders
                WHERE "ShipCountry" = 'France'
                   OR "ShipCountry" = 'Belgium' AND "Freight" > 500) AS rows_returned
        UNION ALL
        SELECT 'with parentheses',
               (SELECT count(*) FROM orders
                WHERE ("ShipCountry" = 'France' OR "ShipCountry" = 'Belgium')
                  AND "Freight" > 500)
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The first line returns 1,783 rows and the second returns 56.

    **The trap.** `AND` is evaluated before `OR`, the way `*` is evaluated before `+`. So
    the first line means "France, or else Belgium with heavy freight", and it hands back
    every French order regardless of freight. Both queries run, both return a plausible
    count, and only one of them answers the question somebody asked.

    Put parentheses around every `OR` you write next to an `AND`, even where they change
    nothing. They cost one character and they remove the reader's need to remember a
    precedence rule.

    👉 **Try it:** predict, then run: how many orders went to France or Belgium with
    freight over 500? Now write the same question with `IN` and check that you get 56.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `IN` and `BETWEEN` are shorthand

    `IN` takes a list and is a tidier `OR`. `BETWEEN` takes two bounds and includes both
    of them.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'IN three countries' AS written,
               (SELECT count(*) FROM orders
                WHERE "ShipCountry" IN ('France', 'Belgium', 'Spain')) AS rows_returned
        UNION ALL
        SELECT 'BETWEEN 20 and 50, products',
               (SELECT count(*) FROM products WHERE "UnitPrice" BETWEEN 20 AND 50)
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `BETWEEN 20 AND 50` includes a product priced at exactly 20 and one priced at exactly
    50. Both ends are in. That is fine for a price and it is a real problem for a date,
    which is §12.

    👉 **Try it:** write the same 31-product filter without `BETWEEN`, using two
    conditions and an `AND`. Confirm the count matches.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Matching a pattern with `LIKE`

    `%` stands for any run of characters, including none. `_` stands for exactly one.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'LIKE ''Ch%''' AS written,
               (SELECT count(*) FROM products WHERE "ProductName" LIKE 'Ch%') AS rows_returned
        UNION ALL
        SELECT 'LIKE ''ch%''',
               (SELECT count(*) FROM products WHERE "ProductName" LIKE 'ch%')
        UNION ALL
        SELECT 'ILIKE ''ch%''',
               (SELECT count(*) FROM products WHERE "ProductName" ILIKE 'ch%')
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    **The trap.** The middle line returns zero, and nothing on the screen says the
    pattern was one keystroke away from six rows. A `LIKE` that comes back empty is a case
    question before it is a data question.

    `ILIKE` is the case-insensitive form. Not every engine has it, and
    `lower("ProductName") LIKE 'ch%'` does the same job everywhere. Case sensitivity here
    is a property of the column called its **collation**, and engines ship different
    defaults for it.

    👉 **Try it:** find every product whose name contains the word `Chef`, then every one
    that ends in `Mix`, then every one whose name is exactly five characters long. The
    last one wants `_` five times, or `length()`.

    ### ✔ Check your understanding

    1. A filter returns 1,783 rows and your colleague expected about 50. Where do you look
       first?
    2. `WHERE "ProductName" LIKE 'ch%'` returns nothing. Name two different explanations
       and explain how you would tell them apart.
    3. `BETWEEN 20 AND 50` on a price column. Which products are on the boundary, and are
       they in or out?
    4. `products.Discontinued` is a `VARCHAR` and `"Discontinued" = 0` still returns 69
       rows in DuckDB. Why is that worth knowing about before you move the query to
       another engine?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 8. `ORDER BY` and `LIMIT`

    Chapter A §1 said the order of the rows is not part of the table. `ORDER BY` is how
    you ask for one. `LIMIT` cuts the result short after the sorting is done, and the two
    of them almost always travel together.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `ORDER BY` and `DESC`

    Ascending is the default, so `ORDER BY "UnitPrice"` starts at the cheapest. `DESC`
    starts at the most expensive.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductName", round("UnitPrice", 2) AS price
        FROM products
        ORDER BY "UnitPrice" DESC
        LIMIT 5
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `Côte de Blaye` at 263.50 is more than twice the price of the next product down, which
    is the sort of thing you find in the first minute of looking and not in a summary.

    👉 **Try it:** run the same query without `DESC` and read the first five. Then state
    which of the two lists you would put in front of a buyer.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A second sort key breaks the ties

    Sort by country and the customers inside each country come back in whatever order the
    engine finds convenient. A second column decides that too.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "Country", "City", "CompanyName"
        FROM customers
        ORDER BY "Country", "CompanyName"
        LIMIT 8
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The three Argentinian customers are alphabetical by company because of the second key.
    Each key can take its own direction: `ORDER BY "Country", "CompanyName" DESC` sorts
    countries up and companies down inside each one.

    👉 **Try it:** sort `orders` by `"ShipCountry"` and then by `"Freight"` descending,
    and take the first 10. Which country did you get, and is that a fact about freight or
    a fact about the alphabet?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### You can sort by a name you invented in `SELECT`

    `doubled` does not exist in `products`. It exists because this query made it, and
    `ORDER BY` can still use it.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductName", round("UnitPrice" * 2, 2) AS doubled
        FROM products
        ORDER BY doubled DESC
        LIMIT 5
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    That works because `SELECT` has already run by the time `ORDER BY` is applied, which
    is §16 and is worth coming back to. For now it is a convenience: name the expression
    once and sort by the name.

    👉 **Try it:** compute `"UnitsInStock" * "UnitPrice" AS stock_value`, sort by it
    descending, and take the top 5. Then work out what share of the whole inventory's value
    those five hold, with `SELECT round(sum("UnitsInStock" * "UnitPrice"), 2) FROM products`
    for the whole. *Check yourself: about a quarter, 25.1%.*
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `LIMIT` without `ORDER BY` means nothing in particular

    `LIMIT 3` returns three rows. Which three is up to the engine.
    """)
    return


@app.cell
def _(con):
    con.sql("""SELECT "ProductName", round("UnitPrice", 2) AS price FROM products LIMIT 3""")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Those happen to be the first three rows in the parquet file. Nothing promised that,
    and a database that stored the same table differently is entitled to hand you three
    others.

    **The trap.** "The top 3 products" written as `LIMIT 3` with no `ORDER BY` is a claim
    about the business made from an accident of storage. It produces three rows, they look
    like an answer, and there is nothing on the screen to say they are arbitrary. Any
    `LIMIT` you show somebody needs an `ORDER BY` above it.

    `OFFSET` skips before it takes, which is how you get the second page.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductName", round("UnitPrice", 2) AS price
        FROM products
        ORDER BY "UnitPrice" DESC
        LIMIT 5 OFFSET 5
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Products six to 10 by price. `OFFSET` without `ORDER BY` has the same problem as
    `LIMIT` without one, and worse, because two pages of an unsorted result can repeat a
    row and miss another.

    👉 **Try it:** get products 11 to 15 by price. Then run the same query twice and
    confirm you get the same five rows both times.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Where the missing values sort

    `employees.ReportsTo` has one NULL in nine rows. Sorting on that column has to put it
    somewhere.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "EmployeeID", "LastName", "ReportsTo"
        FROM employees
        ORDER BY "ReportsTo"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    DuckDB puts NULLs last in an ascending sort and last again in a descending one, so the
    row you cannot rank stays out of the way at the bottom either way. That is a choice the
    engine made, and engines make it differently, so a query moved somewhere else can put
    the same row at the top.

    `NULLS FIRST` and `NULLS LAST` say it out loud and travel between engines.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "EmployeeID", "LastName", "ReportsTo"
        FROM employees
        ORDER BY "ReportsTo" NULLS FIRST
        LIMIT 3
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    **The trap.** `ORDER BY "ReportsTo" DESC LIMIT 1` to find the highest manager id gives
    you employee 6, and the one person who reports to nobody is the row you were actually
    looking for. §9 is what a missing value does to everything else.

    👉 **Try it:** sort `orders` by `"ShippedDate"` descending and take the first five.
    Then add `NULLS FIRST` and take the first five again. State what each of the two lists
    is a list of.

    ### ✔ Check your understanding

    1. A report shows "our five biggest orders" and the query ends `LIMIT 5` with no
       `ORDER BY`. What is wrong with the report, and would you be able to see it in the
       output?
    2. You sort by `"Country"` alone and run the query on two different machines. Can the
       rows inside `Germany` come back in a different order?
    3. `ORDER BY "ShippedDate" DESC LIMIT 1` on `orders`. What do you get, and what did
       you probably want?
    4. What does `LIMIT 5 OFFSET 5` give you, and what has to be true for the answer to
       be stable?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 9. `NULL`, and why it is not zero

    `NULL` is what a database puts in a cell when it has no value for it. It is not zero,
    it is not an empty string, and it is not `FALSE`. It means the value is unknown, and
    SQL takes that literally, so anything you ask about an unknown value comes back
    unknown, which is the third answer alongside true and false in SQL's
    **three-valued logic**.

    Every trap in this section follows from that one sentence.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A missing value in `employees`

    `employees` is small enough to read whole. Andrew Fuller is the Vice President and he
    reports to nobody, so his `ReportsTo` is `NULL`.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "EmployeeID", "FirstName", "LastName", "Title", "ReportsTo"
        FROM employees
        ORDER BY "EmployeeID"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    A zero in that column would be a claim that he reports to employee 0, and no such
    person exists. The `NULL` says the database has nothing to put there, and the reason
    here is structural, because somebody has to be at the top.

    Three different situations all produce a `NULL` and only the third is a data quality
    problem.

    | | example in this warehouse | what it means |
    |---|---|---|
    | it cannot apply | `employees.ReportsTo`, 1 of 9 | the VP has no manager |
    | it has not happened yet | `orders.ShippedDate`, 21 of 16,282 | placed and never shipped |
    | nobody recorded it | `customers.Region`, 2 of 93 | the address has a region and the row does not |

    ### In Excel terms

    A blank cell in Excel behaves as zero the moment you compare it to something, so
    `=IF(A1>10,...)` on a blank cell is false and `=A1+100` is 100. SQL refuses both. That
    difference is the reason a spreadsheet total and a database total disagree on data
    with holes in it, and the spreadsheet is the one that answered without being asked.

    👉 **Try it:** count the NULLs in each of the three columns above using
    `count(*) - count(<column>)`. Predict all three numbers first.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Why `= NULL` finds nothing

    Asking whether an unknown value equals `NULL` asks whether one unknown thing is the
    same as another. SQL answers "unknown", `WHERE` keeps only rows that answered true,
    and the row is dropped.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'WHERE "ReportsTo" = NULL' AS written,
               (SELECT count(*) FROM employees WHERE "ReportsTo" = NULL) AS rows_returned
        UNION ALL
        SELECT 'WHERE "ReportsTo" IS NULL',
               (SELECT count(*) FROM employees WHERE "ReportsTo" IS NULL)
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    **The trap.** The first query is not an error. It runs, it returns zero rows, and zero
    rows reads exactly like "there are none", which is the answer you would have got from
    a table with no missing values at all. The operators are `IS NULL` and `IS NOT NULL`,
    and they are the only ones that work.

    👉 **Try it:** find the orders that have never shipped, once with `= NULL` and once
    with `IS NULL`. One of them gives you 21 rows.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A `NULL` spreads through anything it touches

    Arithmetic on an unknown value is unknown. So is a comparison.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 100 + NULL       AS hundred_plus_null,
               NULL = NULL      AS null_equals_null,
               NULL <> NULL     AS null_not_equals_null,
               coalesce(NULL, 0) AS coalesced
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `100 + NULL` is `NULL`, because if you do not know one of the numbers you do not know
    the total. `NULL = NULL` evaluates to `NULL`, and so does `NULL <> NULL`. A missing
    value is neither equal nor unequal to anything.

    `coalesce(a, b)` returns the first of its arguments that is not `NULL`, so
    `coalesce("ReportsTo", 0)` turns the VP's missing manager into a zero.

    **The trap.** That is a decision, and it is invisible three steps later. Once the
    `NULL` is a zero, every count includes it, every average is pulled toward it, and
    nothing on the screen says a substitution happened. Use `coalesce` where zero is
    genuinely the right value, such as a quantity nobody ordered. Do not use it to make a
    report stop showing blanks.

    👉 **Try it:** run `SELECT "EmployeeID", "ReportsTo", coalesce("ReportsTo", 0) AS
    filled FROM employees ORDER BY "EmployeeID"`, then take the average of both columns
    and state which of the two numbers you would put in a memo.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A `NULL` falls out of both sides of a filter

    Eight employees report to somebody. Five report to employee 2 and three report to
    employee 5, so `<> 2` should find three people, plus the VP who reports to nobody.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'WHERE "ReportsTo" = 2' AS written,
               (SELECT count(*) FROM employees WHERE "ReportsTo" = 2) AS rows_returned
        UNION ALL
        SELECT 'WHERE "ReportsTo" <> 2',
               (SELECT count(*) FROM employees WHERE "ReportsTo" <> 2)
        UNION ALL
        SELECT 'WHERE "ReportsTo" <> 2 OR "ReportsTo" IS NULL',
               (SELECT count(*) FROM employees
                WHERE "ReportsTo" <> 2 OR "ReportsTo" IS NULL)
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Five and three make eight, and the table has nine rows. The VP is in neither result.
    He fails `= 2` because his value is unknown, and he fails `<> 2` for the same reason.

    **The trap.** Split a table on a condition and its opposite, and the two halves do not
    add up to the whole. On nine rows you notice. On 16,282 you do not, and the missing
    rows are silently the ones with the most interesting story, because a value goes
    missing for a reason.

    👉 **Try it:** count the orders shipped later than required, then the ones shipped on
    time or earlier, and add the two. Compare to 16,282 and account for the difference.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### The orders that never shipped

    This is the one in the warehouse worth knowing about.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)                        AS all_orders,
               count("ShippedDate")            AS have_a_ship_date,
               count(*) - count("ShippedDate") AS never_shipped
        FROM orders
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `count(*)` counts rows and `count("ShippedDate")` counts rows where that column has a
    value, so the gap between them is the count of NULLs. §13 comes back to that pair.

    There are 21 orders that were placed and have no ship date. Treating that as a
    shipping time of zero days would make the delivery numbers look better; treating it as
    a very long delivery time would make them look worse. Both are decisions, and the honest report
    names them and says how many rows are involved.

    A word about the column that is not here. `orders.ShipRegion` is the famous NULL
    example in every Northwind tutorial written before this one. In this build somebody
    filled it in, and it has zero NULLs, so an inherited example runs perfectly and
    teaches nothing.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*) - count("ShipRegion") AS shipregion_nulls,
               count(*) - count("ShippedDate") AS shippeddate_nulls
        FROM orders
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    👉 **Try it:** run `count(*) - count(<column>)` over five columns of `orders` and find
    every one with a NULL in it. State, for each, which of the three situations in the table
    above it is.

    ### ✔ Check your understanding

    1. A column is blank in Excel and `NULL` in the database. Give one calculation that
       returns a different answer in each.
    2. `WHERE "ShippedDate" = NULL` returns zero rows and `WHERE "ShippedDate" IS NULL`
       returns 21. Which one is broken, and how would somebody reading the report know?
    3. You filter on `"Country" <> 'USA'` and get fewer rows than you expected. What is
       the first thing to check?
    4. Somebody wraps a column in `coalesce(col, 0)` so the report has no blanks. Name
       two numbers downstream that just changed.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 10. `DISTINCT`, and what it hides

    `DISTINCT` removes duplicate rows from a result. It is the shortest fix for a list
    that repeats itself, it applies to the whole row and not to one column of it, and it is
    worth being suspicious of every time you reach for it.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### One row per different value

    `customers` has 93 rows. Asking for the country column gives 93 rows, most of them
    repeats.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*) AS customer_rows,
               (SELECT count(*) FROM (SELECT DISTINCT "Country" FROM customers))
                   AS distinct_countries
        FROM customers
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    So the query returns 22 rows for 93 customers. `DISTINCT` goes immediately after
    `SELECT` and applies to everything after it.

    The row count is not the country count. Two customers have no `Country`, and §9
    already said a `NULL` is a value the row carries, so
    `DISTINCT` keeps one `NULL` row alongside the 21 real names. Run
    `count(DISTINCT "Country")` on the same column and it answers 21, because an
    aggregate skips `NULL` and `DISTINCT` does not. **The memo sentence says 21.**
    """)
    return


@app.cell
def _(con):
    con.sql("""SELECT DISTINCT "Country" FROM customers ORDER BY "Country" LIMIT 8""")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    👉 **Try it:** list the distinct `"ShipCountry"` values in `orders` and count them.
    Then run `count(DISTINCT "ShipCountry")` beside it. The two agree here and they
    disagreed on `customers`. Find the column difference that explains why, without
    reading it above.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Adding a column un-deduplicates the result

    `DISTINCT` applies to the whole row, so two rows have to match in every selected
    column before one of them is dropped. Adding a column can only ever increase the
    number of rows you get back.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'SELECT DISTINCT "Country"' AS written,
               (SELECT count(*) FROM (SELECT DISTINCT "Country" FROM customers))
                   AS rows_returned
        UNION ALL
        SELECT 'SELECT DISTINCT "Country", "City"',
               (SELECT count(*) FROM (SELECT DISTINCT "Country", "City" FROM customers))
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The count goes from 22 to 70. Both results are correct and they answer different
    questions. The first is a list of countries and the second is a list of places.

    **The trap.** `SELECT DISTINCT` on a query with 10 columns in it removes almost
    nothing, because 10 columns have to agree before two rows count as the same. A
    `DISTINCT` that changed the row count by three on a result of 4,000 has not cleaned
    anything up; it has hidden three rows you have not looked at.

    👉 **Try it:** run `SELECT DISTINCT * FROM orders` and compare the count to 16,282.
    Then explain what a difference of zero tells you, and what a difference of 40 would have
    told you.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `count(DISTINCT x)` against `count(x)`

    The two counts answer different questions and the difference is the point.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)                     AS lines,
               count(DISTINCT "OrderID")    AS orders_covered,
               count(DISTINCT "ProductID")  AS products_covered
        FROM "Order Details"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    There are 609,283 order lines, belonging to 16,282 orders and mentioning 77 different
    products. That third number is every product in the catalog, so every one of them appears on at
    least one line.

    An order line count and an order count are both true and only one of them answers
    "how many orders did we take". Chapter C §19 is where confusing the two starts costing
    real money, because a join can turn one order into six.

    👉 **Try it:** on `orders`, get `count(*)`, `count(DISTINCT "CustomerID")` and
    `count(DISTINCT "ShipCountry")` in one query. Write the three numbers as one English
    sentence about the business.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `DISTINCT` and `count(DISTINCT ...)` disagree about `NULL`

    `customers.Region` has two NULLs. `SELECT DISTINCT` treats them as one more value to
    list, and `count(DISTINCT ...)` ignores them the way every aggregate does.
    """)
    return


@app.cell
def _(con):
    con.sql("""SELECT DISTINCT "Region" FROM customers ORDER BY "Region" """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)                    AS customer_rows,
               count("Region")             AS region_not_null,
               count(DISTINCT "Region")    AS distinct_regions,
               (SELECT count(*) FROM (SELECT DISTINCT "Region" FROM customers))
                   AS distinct_rows
        FROM customers
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `SELECT DISTINCT "Region"` returns 10 rows and the last one is `NULL`.
    `count(DISTINCT "Region")` returns 9. Same column, same query, one row of difference,
    and no message about it.

    **The trap.** "We operate in nine regions" and "the region column takes 10 different
    forms" are both defensible, and a slide carrying one of them was produced by a query
    that made the choice for you. §13 says why every aggregate behaves this way.

    👉 **Try it:** on `orders`, run `count(DISTINCT "ShippedDate")` and then
    `SELECT count(*) FROM (SELECT DISTINCT "ShippedDate" FROM orders)`. Explain the
    difference of one.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A `DISTINCT` you needed is usually a symptom

    A query returns duplicates for a reason, and there are three.

    - **The column genuinely repeats.** 93 customers live in 21 countries, so a list of
      countries repeats. Here `DISTINCT` is the right tool and there is nothing to
      investigate.
    - **You joined to a table that has more than one matching row.** One order with six
      lines becomes six rows, and `DISTINCT` collapses them back while quietly discarding
      the reason there were six. Chapter C §19 is that whole story.
    - **The table has duplicate rows in it.** Somebody loaded the same file twice.
      `DISTINCT` makes the symptom disappear from your screen and leaves it in the table
      for the next person.

    A `DISTINCT` added to make a number look right is a note to yourself that you have not
    found out why the number was wrong. Before you add one, run the query without it and
    look at a set of the duplicates.

    👉 **Try it:** take one `"OrderID"` from `"Order Details"` and select all its rows.
    Now run `SELECT DISTINCT "OrderID" FROM "Order Details" WHERE "OrderID" = <that id>`.
    You get one row. Name what you can no longer see.

    ### ✔ Check your understanding

    1. `SELECT DISTINCT "Country"` gives 22 rows and `SELECT DISTINCT "Country", "City"`
       gives 70. Explain the difference to somebody who has never written SQL.
    2. `count(DISTINCT "Region")` is 9 and the distinct list is 10 rows long. Which number
       goes in a memo, and what sentence has to go with it?
    3. A colleague adds `DISTINCT` and the row count drops from 4,000 to 3,997. What
       should they do next?
    4. When is `DISTINCT` the right answer, and when is it a sign that something needs
       investigating?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 11. `CASE WHEN`

    `CASE WHEN` is SQL's `if`. It tests conditions in order, stops at the first one that
    is true, and returns the value attached to it. It is how a continuous number becomes a
    band, how a code becomes a label, and how you count something conditionally inside an
    aggregate.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### The shape

    A `CASE` expression is one column of the result. It runs once per row.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT CASE WHEN "UnitPrice" < 20 THEN 'budget'
                    WHEN "UnitPrice" < 50 THEN 'mid'
                    ELSE 'premium'
               END AS price_band,
               count(*) AS n_products
        FROM products
        GROUP BY price_band
        ORDER BY n_products DESC
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The 77 products come out as 39 budget, 31 mid and 7 premium, divided up by a rule you
    invented in the query. The rule is nowhere in the table, and anybody reading the
    number has to be told what the boundaries were.

    `GROUP BY price_band` is §14 arriving early. Read it as "one row per band" and come
    back.

    👉 **Try it:** band `orders` by freight, using 100 and 400 as the boundaries, and
    count each band. Then move the lower boundary to 150 and state how much the picture
    changed.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### The first true branch wins

    Conditions are tested top to bottom and testing stops at the first hit. Swap two lines
    that overlap and the answer changes without anything looking wrong.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT CASE WHEN "UnitPrice" < 50 THEN 'mid'
                    WHEN "UnitPrice" < 20 THEN 'budget'
                    ELSE 'premium'
               END AS price_band,
               count(*) AS n_products
        FROM products
        GROUP BY price_band
        ORDER BY n_products DESC
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    **The trap.** `budget` is gone. Every product under 20 is also under 50, so the first
    branch claims it and the second is unreachable. The query runs, returns two bands
    instead of three, and the only sign is a category that silently holds zero rows.

    When bands overlap, order them from the narrowest condition to the widest. When they
    do not overlap, the order does not matter and writing them in a sensible sequence
    still helps whoever reads it.

    👉 **Try it:** write a three-band `CASE` where the branches overlap on purpose, run
    it, then reorder the branches and run it again. Predict the second result before you
    look.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### No `ELSE` means `NULL`

    `ELSE` is optional. Leaving it out gives every unmatched row a `NULL` in that column.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT CASE WHEN "UnitPrice" < 20 THEN 'budget'
                    WHEN "UnitPrice" < 50 THEN 'mid'
               END AS price_band,
               count(*) AS n_products
        FROM products
        GROUP BY price_band
        ORDER BY n_products DESC
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The 7 premium products are still there and their band is `NULL`. In a grouped result
    they are visible as a row labeled `NULL`, which is lucky. In a filtered one they are
    gone, because §9 already showed that a `NULL` fails every comparison.

    Write an `ELSE` every time, even where you are sure the branches cover everything.
    `ELSE 'other'` costs one line and turns a silent hole into a labeled one.

    👉 **Try it:** wrap that query in `SELECT sum(n_products) FROM (...) WHERE price_band
    IS NOT NULL` and watch the total come to 70 instead of 77. Then put the `ELSE` back
    and run it again.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `sum(CASE WHEN ... THEN 1 ELSE 0 END)` counts a condition

    This is the most used `CASE` in business SQL. It puts a 1 on every row that matches
    and a 0 on every row that does not, and the sum is the count of matches. Because it is
    an aggregate over the same rows as `count(*)`, you get the count and the share in one
    query.

    An order is late when it shipped after the date it was required.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*) AS n_orders,
               sum(CASE WHEN "ShippedDate" > "RequiredDate" THEN 1 ELSE 0 END) AS late,
               round(100.0 * sum(CASE WHEN "ShippedDate" > "RequiredDate" THEN 1 ELSE 0 END)
                     / count(*), 1) AS pct_late
        FROM orders
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Of 16,282 orders, 3,755 shipped late, which is 23.1 percent. That is a business fact
    somebody would want on a slide, and it took one query.

    The `100.0` is deliberate. Written as `100`, both sides of the division are integers in
    some engines and the answer comes back as a whole number of percent.

    👉 **Try it:** get the count and the percentage of orders where freight was over 400.
    Then do the same thing with a `WHERE` clause and two separate queries, and state which
    version you would rather maintain.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### What that `ELSE 0` did to the 21

    `ShippedDate` is `NULL` for 21 orders. `NULL > "RequiredDate"` is `NULL`, which is not
    true, so those rows fall into the `ELSE` and are counted as 0. They were counted as
    not late.

    The query below counts the three groups separately with `FILTER (WHERE ...)`, which
    applies a condition to one aggregate and leaves the others alone.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*) FILTER (WHERE "ShippedDate" <= "RequiredDate") AS on_time,
               count(*) FILTER (WHERE "ShippedDate" >  "RequiredDate") AS late,
               count(*) FILTER (WHERE "ShippedDate" IS NULL)           AS never_shipped,
               count(*)                                               AS total
        FROM orders
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The three counts are 12,506 on time, 3,755 late and 21 never shipped, and they add to
    16,282. The two-way split hid the third group inside the first one.

    **The trap.** Every two-branch `CASE` over a column that can be `NULL` puts the
    unknowns in one branch, and it is always the `ELSE`. An order that never shipped is
    the worst outcome the business has and this query filed it under "on time".

    Write the third branch:

    ```sql
    CASE WHEN "ShippedDate" IS NULL          THEN 'never shipped'
         WHEN "ShippedDate" > "RequiredDate" THEN 'late'
         ELSE 'on time'
    END
    ```
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT CASE WHEN "ShippedDate" IS NULL          THEN 'never shipped'
                    WHEN "ShippedDate" > "RequiredDate" THEN 'late'
                    ELSE 'on time'
               END AS delivery_status,
               count(*) AS n_orders
        FROM orders
        GROUP BY delivery_status
        ORDER BY n_orders DESC
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The `IS NULL` branch goes first because the branches overlap in the way the previous
    knowledge point described. An unshipped order fails the second test, so it would land
    in `ELSE` if the first branch were not there to catch it.

    👉 **Try it:** build the same three-way status for `customers.Region`, with branches
    for `NULL`, for `'Western Europe'` and for everything else. Check the three counts add
    to 93.

    ### ✔ Check your understanding

    1. A `CASE` with three branches returns only two labels. What happened?
    2. Why does `sum(CASE WHEN cond THEN 1 ELSE 0 END)` give you a count, and what does it
       give you that a `WHERE` clause does not?
    3. Your `CASE` has no `ELSE`. Where do the unmatched rows show up in a grouped result,
       and where do they disappear?
    4. The on-time and late counts add to 16,261 and the table has 16,282 rows. Where are
       the other 21, and which of the two labels would a two-branch `CASE` have given them?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 12. Dates: truncating, differencing, grouping by month

    Two queries below run without an error, return a plausible number, and are wrong. Both
    are wrong for the same reason, and it is the first thing to check about any date column
    you are handed.

    📖 **Used in:** Building and querying your database · Building your product
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A timestamp with a clock time

    `orders` has three date columns and `DESCRIBE` calls all three `TIMESTAMP`. A
    timestamp is a date and a time of day, down to the second.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*) FILTER (WHERE "OrderDate" = date_trunc('day', "OrderDate"))
                   AS at_midnight,
               count(*) FILTER (WHERE "OrderDate" <> date_trunc('day', "OrderDate"))
                   AS with_a_clock_time,
               count(*) AS total,
               min("OrderDate") AS earliest,
               max("OrderDate") AS latest
        FROM orders
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Exactly 830 orders sit at midnight and 15,452 carry a real time of day. The 830 are the
    block from `OrderID` 10248 to 11077, the original Northwind rows that came with a date
    and no time; everything after that was generated with a clock.

    The data runs from 2012-07-10 to 2023-10-28. Anything you read about Northwind
    elsewhere is written against 1996 to 1998 and returns zero rows here.

    👉 **Try it:** confirm the 830 by running
    `SELECT min("OrderID"), max("OrderID"), count(*) FROM orders WHERE "OrderDate" =
    date_trunc('day', "OrderDate")`.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `=` against a timestamp

    How many orders were placed on 4 July 2016? The obvious query and the right one differ
    by six characters.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'WHERE "OrderDate" = DATE ''2016-07-04''' AS written,
               (SELECT count(*) FROM orders
                WHERE "OrderDate" = DATE '2016-07-04') AS rows_returned
        UNION ALL
        SELECT 'WHERE "OrderDate"::DATE = DATE ''2016-07-04''',
               (SELECT count(*) FROM orders
                WHERE "OrderDate"::DATE = DATE '2016-07-04')
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The first form finds one row and the second finds 6. The first query compared a
    timestamp to a date, which SQL reads as
    a comparison against midnight on that date, so it found the single order that happened
    to have no clock time. It is off by five sixths and there is nothing on the screen to
    say so.

    `::DATE` throws the time away before comparing. `CAST("OrderDate" AS DATE)` is the same
    thing written portably.

    👉 **Try it:** run both forms for 2016-07-08 and for 2017-03-15. Predict which dates
    give you the same answer twice before you run them.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `BETWEEN` on a timestamp

    §7 said `BETWEEN` includes both ends, and on a price that was fine. On a timestamp the
    upper end is midnight, so a whole day goes missing from a range whose name contains
    that day.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'BETWEEN ''2016-07-01'' AND ''2016-07-31''' AS written,
               (SELECT count(*) FROM orders
                WHERE "OrderDate" BETWEEN DATE '2016-07-01'
                                      AND DATE '2016-07-31') AS rows_returned
        UNION ALL
        SELECT '>= ''2016-07-01'' AND < ''2016-08-01''',
               (SELECT count(*) FROM orders
                WHERE "OrderDate" >= DATE '2016-07-01'
                  AND "OrderDate" <  DATE '2016-08-01')
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The two forms return 146 and 149. Four orders were placed on 31 July, three of them
    carry a clock time, and those three are what fell out.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "OrderID", "OrderDate", "ShipCountry"
        FROM orders
        WHERE "OrderDate" >  DATE '2016-07-31'
          AND "OrderDate" <  DATE '2016-08-01'
        ORDER BY "OrderDate"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    **The trap.** A monthly report built on `BETWEEN` undercounts every month, by however
    many orders came in after midnight on the last day. The shortfall here is about 2
    percent, it is in the same direction every month, and no engine reports it.

    Write date ranges half open: `>=` the start and `<` the day after the end. The upper
    bound is a date you are excluding, which reads oddly for about a week and then stops
    being a decision you make each time.

    ```sql
    WHERE "OrderDate" >= DATE '2016-07-01' AND "OrderDate" < DATE '2016-08-01'
    ```

    👉 **Try it:** count the orders in February 2016 with `BETWEEN` and with the half-open
    form. Then do 2016-02-01 to 2016-02-29 and explain why February is the month where this
    also goes wrong for a second reason.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Truncating with `date_trunc`

    `date_trunc('month', ts)` returns the first instant of that month. Every timestamp in
    July 2016 truncates to `2016-07-01 00:00:00`, which is what makes grouping by month
    work at all.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT date_trunc('month', "OrderDate") AS month_start,
               count(*) AS n_orders
        FROM orders
        WHERE "OrderDate" >= DATE '2016-07-01' AND "OrderDate" < DATE '2017-01-01'
        GROUP BY month_start
        ORDER BY month_start
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    July's 149 matches the half-open count above, which is the check worth doing whenever
    you group by a truncated date.

    The units are `'year'`, `'quarter'`, `'month'`, `'week'`, `'day'` and `'hour'`.
    `strftime` returns a string instead, which is what a chart axis wants.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT strftime("OrderDate", '%Y-%m') AS month_label,
               count(*) AS n_orders
        FROM orders
        WHERE "OrderDate" >= DATE '2016-07-01' AND "OrderDate" < DATE '2016-10-01'
        GROUP BY month_label
        ORDER BY month_label
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `'%Y-%m'` sorts correctly as text because the year comes first. `'%m/%Y'` does not, and
    a chart built on it puts January of every year next to each other.

    **The trap.** Grouping on the raw timestamp looks like it worked and gives you one
    group per second.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'GROUP BY "OrderDate"' AS grouped_on,
               (SELECT count(*) FROM (SELECT "OrderDate" FROM orders
                                      GROUP BY "OrderDate")) AS n_groups
        UNION ALL
        SELECT 'GROUP BY "OrderDate"::DATE',
               (SELECT count(*) FROM (SELECT "OrderDate"::DATE FROM orders GROUP BY 1))
        UNION ALL
        SELECT 'GROUP BY date_trunc(''month'', "OrderDate")',
               (SELECT count(*) FROM (SELECT date_trunc('month', "OrderDate")
                                      FROM orders GROUP BY 1))
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Grouping on the raw timestamp gives 15,932 groups over 16,282 rows, against 4,053 days
    and 136 months for the other two. The first result is a table with almost one row per
    order, and on a screen it looks like a very detailed report.

    👉 **Try it:** count orders per year with `date_trunc`, then again with
    `date_part('year', "OrderDate")`. Confirm the two agree, and state which one you would
    put on the x axis of a chart.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Differencing with `date_diff`

    `date_diff(unit, start, end)` gives the number of unit boundaries between the two.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "OrderID",
               "OrderDate"::DATE   AS ordered,
               "ShippedDate"::DATE AS shipped,
               date_diff('day', "OrderDate", "ShippedDate") AS days_to_ship,
               round("Freight", 2) AS freight,
               "ShipCountry"
        FROM orders
        WHERE "OrderID" BETWEEN 10248 AND 10252
        ORDER BY "OrderID"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The five orders went to four countries and took 12, 5, 4, 7 and 2 days to ship. These
    rows come back throughout the course. Read them once by eye.

    **The trap.** `date_diff('day', ...)` counts midnight crossings, so two timestamps two
    hours apart return 1 day when the midnight is between them, and two timestamps 22 hours
    apart return 0 days when it is not.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT date_diff('day', TIMESTAMP '2016-07-04 23:00:00',
                                TIMESTAMP '2016-07-05 01:00:00') AS two_hours_apart,
               date_diff('day', TIMESTAMP '2016-07-04 01:00:00',
                                TIMESTAMP '2016-07-04 23:00:00') AS twenty_two_hours_apart
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    That is the right answer for "how many days later", and it is the wrong one for "how
    long did it take". For elapsed time, turn the gap into seconds with `epoch()` and
    divide. `epoch("ShippedDate" - "OrderDate") / 86400` is the gap in days, and dividing
    by 3,600 gives hours. Keep the `epoch()`, because the subtraction alone returns an
    `INTERVAL`, which a marimo SQL cell cannot turn into a table.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT round(epoch(TIMESTAMP '2016-07-05 01:00:00'
                           - TIMESTAMP '2016-07-04 23:00:00') / 86400, 2) AS two_hours_apart,
               round(epoch(TIMESTAMP '2016-07-04 23:00:00'
                           - TIMESTAMP '2016-07-04 01:00:00') / 86400, 2) AS twenty_two_hours_apart
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Measured this way, the same two pairs are 0.08 and 0.92 days apart. Averaged over the
    warehouse, the day count from `date_diff` is stable enough to quote.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count("ShippedDate") AS shipped_orders,
               round(avg(date_diff('day', "OrderDate", "ShippedDate")), 2) AS mean_days,
               median(date_diff('day', "OrderDate", "ShippedDate")) AS median_days
        FROM orders
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The mean is 7.85 days and the median is 6, over the 16,261 orders that shipped. The 21
    that never shipped are absent from both numbers, which §13 explains and §9 already warned
    about.

    👉 **Try it:** find the five orders with the longest gap between `"OrderDate"` and
    `"ShippedDate"`. Then check whether any of them is one of the 21, and state what your
    query did with those rows.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Pulling one piece out

    `date_part` returns a number and `dayname` returns a word. Use both for grouping and
    labeling. A filter written on a date part has to compute the part for every row before
    it can compare anything, which is why §12 opened on ranges.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "OrderID",
               "OrderDate",
               date_part('year',  "OrderDate") AS yr,
               date_part('month', "OrderDate") AS mo,
               dayname("OrderDate")            AS weekday,
               current_date                    AS today
        FROM orders
        WHERE "OrderID" BETWEEN 10248 AND 10250
        ORDER BY "OrderID"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `current_date` is today according to the machine running the query, so a report that
    uses it gives a different answer tomorrow. That is usually what you want and it makes
    the result impossible to reproduce, so pin the date in anything you are asked to
    defend.

    Date functions are where engines disagree most, and the names are the disagreement.
    SQL Server writes `GETDATE()` and `DATEDIFF(DAY, a, b)` where DuckDB writes
    `current_date` and `date_diff('day', a, b)`. Check the function names first whenever a
    query written for one engine will not parse in another.

    👉 **Try it:** count the orders by day of the week and judge whether the pattern is a
    fact about the business or a fact about how this data was generated. `dayname` and a
    `GROUP BY` are all you need. Type the number of orders placed on a Sunday into the box
    at the end of this section. The box answers only when you run this chapter on your own
    computer.

    ### ✔ Check your understanding

    1. `WHERE "OrderDate" = DATE '2016-07-04'` returns one row. Why is that number wrong,
       and why does nothing warn you?
    2. Write the half-open range for the whole of March 2016. What is the upper bound, and
       why is it a date in April?
    3. A monthly chart built with `BETWEEN` and one built with `>=` and `<` differ by about
       3 percent in most months. Which is right, and how would you show somebody?
    4. `date_diff('day', ...)` between 11pm and 1am the next night returns 1. When is that
       the answer you want, and when is it not?
    """)
    return


@app.cell
def _(mo):
    found_it_box = mo.ui.text(label="Your number")
    found_it_box
    return (found_it_box,)


@app.cell
def _(found_it_box, mo):
    import base64 as _base64
    import hashlib as _hashlib

    _key = found_it_box.value.strip().replace(",", "")
    _lock = "e2167e3c1b1afe4fc1a81a51d3c18ed85f31e8449288ad63f80a6b0d2a5ed179"
    _box = _base64.b64decode("8gIgJQZaYfuZ1WiU9nvCdQv0dsxrTS8xqqDLmVS8gr2WKM3IN6d3IW+DEdrR9IsIteeAQAsbmk8zPtNQuBDJ7+3/vdPoPhwXbs4IxiIGMqZG8maz1YkmgIa6ZzPrOAjItfxvr6CdYUpG7lIIHxXdA3fyKluXWKkGXJ7Obupb1hBaxitUZmI12yDCMFeQqCBK0ed0yA6VZWNp0XqoyySunSbNVCjGCh2VhnrjhfyL1NUVL67fdNtLZwgIeIPeNhty4srVBb4=")
    _stream = b"".join(
        _hashlib.sha256(f"pad:{_key}:{_i}".encode()).digest()
        for _i in range(len(_box) // 32 + 1)
    )
    _opened = _hashlib.sha256(f"lock:{_key}".encode()).hexdigest() == _lock
    mo.md(bytes(_a ^ _b for _a, _b in zip(_box, _stream)).decode()) if _opened else None
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 13. Aggregate functions

    An aggregate function reads many rows and returns one value. `count`, `sum`, `avg`,
    `min`, `max` and `median` are the six that cover most business questions. On their own
    they collapse the whole table to a single row; with §14's `GROUP BY` they collapse each
    group to a row.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### From many rows to a single row

    This runs six aggregates over all 16,282 orders in one query.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)                     AS n_orders,
               round(sum("Freight"), 2)     AS total_freight,
               round(avg("Freight"), 2)     AS mean_freight,
               round(median("Freight"), 2)  AS median_freight,
               round(min("Freight"), 2)     AS cheapest,
               round(max("Freight"), 2)     AS dearest
        FROM orders
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The result is one row of six columns, with no way back to the rows that produced it.
    That is the trade an aggregate makes.

    You cannot put a plain column next to an aggregate. `SELECT "ShipCountry", count(*)`
    with no `GROUP BY` asks for one country name to stand for 16,282 rows, and §14 shows
    the error you get.

    👉 **Try it:** get the same six numbers for `products.UnitPrice`. Then state which of
    the six you would put in a one-sentence description of the catalog.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `count(*)` and `count(column)` are different questions

    `count(*)` counts rows. `count(x)` counts rows where `x` is not `NULL`.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)             AS rows_in_table,
               count("Freight")     AS freight_present,
               count("ShippedDate") AS ship_date_present
        FROM orders
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The three counts are 16,282, 16,282 and 16,261. The last one is 21 short because 21
    orders never shipped, and subtracting the two is the standard way to count NULLs in a
    column.

    **The trap.** "How many orders do we have" answered with `count("ShippedDate")` gives
    16,261. It is a real number, it is close to right, and it answers a different question.
    Use `count(*)` for rows and `count(column)` when the absence of a value is the thing
    you are measuring.

    👉 **Try it:** on `customers`, run `count(*)`, `count("Region")` and `count("Fax")`.
    One of those three columns is much emptier than the others.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Aggregates skip `NULL`

    `employees.ReportsTo` has eight values and one `NULL`. `avg` sums the eight and divides
    by 8.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)                                     AS rows_,
               count("ReportsTo")                           AS non_null,
               sum("ReportsTo")                             AS total,
               round(avg("ReportsTo"), 3)                   AS avg_,
               round(sum("ReportsTo") * 1.0 / count(*), 3)  AS divided_by_all_rows
        FROM employees
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The two numbers are 3.125 and 2.778. `avg` gave the first, and the second is what you
    get if you decide a missing manager counts as a zero.

    Neither is wrong as arithmetic. They answer "the average among people who have a
    manager" and "the average if we count the VP as reporting to nobody, scored zero",
    and only one of those is a sentence anybody would say out loud.

    👉 **Try it:** compute the average days to ship two ways, once with `avg` over all
    16,282 orders and once after `coalesce`ing the missing ship dates to the order date.
    State which of the two you would defend and to whom.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Aggregates over no rows

    A filter that matches nothing still returns a row, and what is in it depends on the
    function.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)                 AS n_rows,
               sum("Freight")           AS sum_freight,
               avg("Freight")           AS avg_freight,
               coalesce(sum("Freight"), 0) AS sum_with_default
        FROM orders
        WHERE "ShipCountry" = 'Atlantis'
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Zero rows matched. `count(*)` says 0, and `sum` and `avg` both say `NULL`, because
    there was nothing to add and nothing to divide.

    **The trap.** A dashboard tile showing total revenue for a region with no sales prints
    a blank instead of a zero, and a blank reads as "the query broke" to whoever is looking
    at it. `coalesce(sum(x), 0)` is the fix, and it is one of the places where replacing a
    `NULL` with a zero is honest, because a sum over nothing genuinely is nothing.

    👉 **Try it:** run the same query for `'France'` and for a misspelling of it. Compare
    the four columns in each result.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### The average is not the typical

    `avg` is pulled by anything extreme and `median` is not. Where the two disagree,
    something in the data sits a long way from the rest.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)                    AS n_products,
               round(avg("UnitPrice"), 2)  AS mean_price,
               round(median("UnitPrice"), 2) AS median_price,
               round(max("UnitPrice"), 2)  AS most_expensive
        FROM products
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The mean is 28.87 and the median is 19.50. More than half the catalog is under 20 and
    the mean is above 28 because `Côte de Blaye` at 263.50 is pulling on it.

    Freight behaves differently, with a mean of 248.59 and a median of 245.25. That is a
    symmetric spread with no long tail.

    Report both when they disagree, and state which one the sentence is about. "Our average
    product costs 28.87" and "half our products cost under 19.50" are both true and they
    would send a buyer in different directions.

    👉 **Try it:** compute the mean and median of `"Quantity"` in `"Order Details"`. Then
    do the same after excluding the largest 1 percent, and state what changed.

    ### ✔ Check your understanding

    1. `count(*)` is 16,282 and `count("ShippedDate")` is 16,261. What is the 21, and which
       of the two counts answers "how many orders did we take"?
    2. `avg` on a column with missing values divides by what?
    3. A total shows blank instead of 0. What did the query do, and what would you add?
    4. Mean 28.87, median 19.50, max 263.50. Describe the price list in one sentence, and
       state which number you left out and why.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 14. `GROUP BY`

    `GROUP BY` splits the rows into groups and runs the aggregates once per group. One row
    comes back for each distinct combination of the grouping columns, and every row of the
    table lands in exactly one group.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### One row per distinct value

    §13 collapsed 16,282 orders to one row. Grouping by country collapses them to 21.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ShipCountry",
               count(*) AS n_orders,
               round(avg("Freight"), 2) AS avg_freight
        FROM orders
        GROUP BY "ShipCountry"
        ORDER BY n_orders DESC
        LIMIT 10
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    That is the top 10 of 21. The groups partition the table, so the counts add back up to
    the row count you started with, and checking that is the cheapest audit in SQL.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT count(*)  AS n_groups,
               sum(n)    AS rows_covered
        FROM (SELECT "ShipCountry", count(*) AS n FROM orders GROUP BY "ShipCountry")
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    There are 21 groups covering 16,282 rows, which is every row in `orders`. A grouping
    column with `NULL` in it gets a `NULL` group and is still counted, so the total holds
    there too.

    ### In Excel terms

    This is a pivot table. The grouping columns are what you drag into Rows, the aggregates
    are what you drag into Values, and `count(*)` is Count of anything. The difference is
    that a pivot table decides the aggregate for you based on the column type, and here you
    name it, so `sum` of an id column can only happen if you ask for it.

    👉 **Try it:** count orders per `"ShipVia"` on `orders`, then run the same two-number
    audit and confirm the counts add to 16,282.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Every selected column is grouped or aggregated

    A grouped result has one row per group, so every column has to be something a group can
    answer. A grouping column can, because the whole group shares it. An aggregate can,
    because it reads the whole group. A plain column cannot.

    ```sql
    SELECT "CategoryID", "ProductName", count(*)
    FROM products
    GROUP BY "CategoryID"
    ```

    ```
    Binder Error: column "ProductName" must appear in the GROUP BY clause or must be
    part of an aggregate function.
    Either add it to the GROUP BY list, or use "ANY_VALUE(ProductName)" if the exact
    value of "ProductName" is not important.
    ```

    The group for category 1 holds 12 products with 12 different names, and the query asked
    for one name. DuckDB will not guess, and the message tells you the two ways to make
    the query answerable.

    Three ways forward, and they answer different questions: add the column to the
    `GROUP BY` and get a finer grouping, wrap it in an aggregate such as `min` or
    `any_value`, or drop it.

    👉 **Try it:** run that query and read the error. Then fix it three ways and explain what
    each fix means in English.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Grouping on more than one column

    Two grouping columns make one group per combination that exists in the data.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ShipCountry",
               date_part('year', "OrderDate") AS yr,
               count(*) AS n_orders
        FROM orders
        WHERE "ShipCountry" IN ('France', 'Belgium')
          AND "OrderDate" >= DATE '2016-01-01'
          AND "OrderDate" <  DATE '2018-01-01'
        GROUP BY "ShipCountry", yr
        ORDER BY "ShipCountry", yr
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Two countries and two years give four rows, because all four combinations occur. A
    combination that never happens produces no row at all, so a year in which Belgium
    ordered nothing would be absent from this result, with no zero in its place.

    👉 **Try it:** group `orders` by `"ShipCountry"` and `"ShipVia"` and count the rows in
    the result. Work out what the number would be if every combination occurred, compare,
    and explain what a smaller answer would have told you about the business.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### The groups come back in no particular order

    Chapter A §1 said the order of rows is not part of a table, and the same is true of a
    grouped result. The query above ends in `ORDER BY` and that is the only reason the
    countries came out alphabetically.

    Two older descriptions of `GROUP BY` are worth unlearning, because they describe how
    one engine used to implement it. Grouping does not sort its output, and it does not
    merge adjacent rows. It produces one row per distinct combination, in whatever order
    the engine finds them.

    👉 **Try it:** run the country query with the `ORDER BY` deleted, then with
    `ORDER BY "ShipCountry"`, then with `ORDER BY n_orders DESC`. Same 21 rows, three
    arrangements.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Grouping on something you computed

    A grouping column can be an expression. §11's `CASE` band and §12's `date_trunc` are
    both this, and so is anything else you write in `SELECT`.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT CASE WHEN "Freight" < 100 THEN 'light'
                    WHEN "Freight" < 400 THEN 'standard'
                    ELSE 'heavy'
               END AS freight_band,
               count(*) AS n_orders,
               round(avg("Freight"), 2) AS avg_freight,
               round(min("Freight"), 2) AS lightest,
               round(max("Freight"), 2) AS heaviest
        FROM orders
        GROUP BY freight_band
        ORDER BY n_orders DESC
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The bands are yours and the counts are the data's. Check the `min` and `max` inside
    each band against the boundaries you wrote, which is how you catch a band whose
    condition does not say what you meant.

    👉 **Try it:** group `orders` by `date_trunc('year', "OrderDate")` and count them. You
    should get 12 rows spanning the range §12 measured, and the counts should add to
    16,282.

    ### ✔ Check your understanding

    1. A grouped result has 21 rows and the table has 16,282. What has to be true about the
       21 counts?
    2. `SELECT "CategoryID", "ProductName", count(*) ... GROUP BY "CategoryID"` fails. Explain
       in one sentence why, without using the word "error".
    3. Grouping `orders` by country and year gives 252 rows, and there are 21 countries
       and 12 years. What would a result of 240 rows have meant?
    4. Somebody says `GROUP BY` sorts the output. What do you run to show them otherwise?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 15. `HAVING`, against `WHERE`

    Both clauses filter. `WHERE` filters rows before they are grouped and `HAVING` filters
    groups after they are formed. Every explanation of the difference says that much; what
    makes it stick is the query you cannot write.

    Three versions of one question follow. Two of them run and disagree, and the third is
    rejected.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Version 1: `WHERE` throws rows away first

    How many products does each category have? Filtering to the 69 current products first
    means the discontinued ones never reach a group.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "CategoryID", count(*) AS n_products
        FROM products
        WHERE "Discontinued" = '0'
        GROUP BY "CategoryID"
        ORDER BY "CategoryID"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Eight groups, and the counts add to 69. `WHERE` looked at one row at a time and never
    needed to know what a group was.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Version 2: `HAVING` throws groups away last

    Same table, no `WHERE`, and a condition on the group instead. All 77 products are
    grouped, then the small categories are dropped.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "CategoryID", count(*) AS n_products
        FROM products
        GROUP BY "CategoryID"
        HAVING count(*) > 8
        ORDER BY "CategoryID"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Five groups instead of eight. Categories 5, 6 and 7 were formed, counted, found to hold
    7, 6 and 5 products, and discarded. The rows in them were read; the groups did not
    survive.

    `HAVING` can see aggregates because by the time it runs the groups exist and the
    aggregates have been computed. That is the whole of the difference.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Version 3: `WHERE count(*) > 8` is rejected

    The same condition, moved into `WHERE`, does not run.

    ```sql
    SELECT "CategoryID", count(*) AS n_products
    FROM products
    WHERE count(*) > 8
    GROUP BY "CategoryID"
    ```

    ```
    Binder Error: WHERE clause cannot contain aggregates!
    ```

    Read the message as a statement about time. When `WHERE` runs there are no groups yet,
    so there is nothing for `count(*)` to count. The database is not being fussy about
    where you put the condition; it cannot answer it at that point in the query.

    That error is the reason `HAVING` exists. Without it there would be no clause that runs
    after grouping, and no way to ask a question about a group.

    👉 **Try it:** run all three versions. Then move the `HAVING` condition to `WHERE` in a
    query of your own and read the message again, so you recognize it when it turns up in
    something long.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### What each version asks

    Version 1 gave eight groups and version 2 gave five, and both are correct.

    - `WHERE "Discontinued" = '0'` asks: among current products, how many per category?
    - `HAVING count(*) > 8` asks: which categories are large?

    Put them side by side on the same query and the numbers change again.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "CategoryID", count(*) AS n_products
        FROM products
        WHERE "Discontinued" = '0'
        GROUP BY "CategoryID"
        HAVING count(*) > 8
        ORDER BY "CategoryID"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The same five categories, and categories 1 and 2 have dropped from 12 to 11 because the
    discontinued products left before the counting started. The `HAVING` threshold is now
    being compared against a different number, even though the clause is identical to
    version 2.

    Raise the threshold to 11 and the divergence stops being about the counts and starts
    being about which categories come back at all.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT 'HAVING count(*) > 11, all 77 products' AS written,
               (SELECT count(*) FROM (
                    SELECT "CategoryID" FROM products
                    GROUP BY "CategoryID" HAVING count(*) > 11)) AS groups_returned
        UNION ALL
        SELECT 'HAVING count(*) > 11, current products only',
               (SELECT count(*) FROM (
                    SELECT "CategoryID" FROM products WHERE "Discontinued" = '0'
                    GROUP BY "CategoryID" HAVING count(*) > 11))
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The first form keeps four categories and the second keeps two. The `WHERE` filtered no
    group directly. It removed 8 rows, and that pushed two categories under a threshold
    they had cleared a moment before.

    **The trap.** Moving a condition between the two clauses can change the answer without
    changing the row count enough to notice. Decide which one you mean by asking whether
    the condition is a fact about a row or a fact about a group. `Discontinued` is a fact
    about a product. `count(*) > 8` is a fact about a category.

    👉 **Try it:** find the countries with more than 1,000 orders, then the countries with
    more than 1,000 orders whose freight was over 400. State which clause each condition went
    into and why.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `HAVING` without `GROUP BY`

    A query with no `GROUP BY` and an aggregate in it already has a group, which is all
    the rows. `HAVING` filters that one group, so the result is either the row or nothing.
    """)
    return


@app.cell
def _(con):
    con.sql("""SELECT count(*) AS n_products FROM products HAVING count(*) > 8""")
    return


@app.cell
def _(con):
    con.sql("""SELECT count(*) AS n_products FROM products HAVING count(*) > 800""")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The first returns 77 and the second returns no rows at all. It is legal in DuckDB, and
    it is rarely what anybody means. It is worth recognizing because a `GROUP BY` deleted
    by accident from a query that selects only aggregates turns a report into a single
    row or into nothing, with no error.

    👉 **Try it:** take the country query from §14, delete the `GROUP BY` line, add
    `HAVING count(*) > 100`, and predict the output before you run it. Then take
    `"ShipCountry"` out of the `SELECT`, predict again, and run it.

    ### ✔ Check your understanding

    1. `WHERE count(*) > 8` is rejected. Explain the reason in terms of when each clause
       runs, without answering "because that is the rule".
    2. Version 1 gives eight rows and version 2 gives five. Neither is wrong. What are the
       two questions?
    3. You want the categories with more than 8 current products. Which clause holds
       `Discontinued`, which holds the count, and why can neither move?
    4. A query returns one row where you expected 20. What is the first line to look for?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 16. The order the clauses actually run in

    You write a query starting at `SELECT`. The database runs it starting at `FROM`. Every
    rule in this chapter that felt arbitrary follows from that one gap, and once you have
    the run order you can stop memorizing them.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Written order and run order

    | you write | it runs |
    |---|---|
    | `SELECT` | 1. `FROM` |
    | `FROM` | 2. `WHERE` |
    | `WHERE` | 3. `GROUP BY` |
    | `GROUP BY` | 4. `HAVING` |
    | `HAVING` | 5. `SELECT` |
    | `ORDER BY` | 6. `ORDER BY` |
    | `LIMIT` | 7. `LIMIT` |

    Read the right column down: get the rows, throw some away, put the rest into groups,
    throw some groups away, work out the columns, sort what is left, and cut it short.

    Only `SELECT` moved. Take it from the top of the left column, put it after `HAVING`,
    and you have the right column. `SELECT` runs fifth because the expressions in it are
    computed on groups that do not exist until `GROUP BY` has run, and with `SELECT` gone
    from the top, `FROM` comes first, because there is nothing to do until there are rows.

    👉 **Try it:** write out the run order from memory, then check it against the table.
    State, for each of the seven, what has to have happened before it can start.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Why `WHERE` cannot filter on a count

    §15's rejected query is the proof. The run order on this page is what its error
    message was reporting.

    ```sql
    SELECT "CategoryID", count(*) FROM products WHERE count(*) > 8 GROUP BY "CategoryID"
    ```

    ```
    Binder Error: WHERE clause cannot contain aggregates!
    ```

    `WHERE` is step 2 and `GROUP BY` is step 3. At step 2 there are no groups, so `count(*)`
    has nothing to count. The engine is not refusing a badly placed condition; it is
    refusing one that cannot be evaluated yet.

    `HAVING` is step 4 and sees aggregates for the same reason in reverse. Everything in
    §15 is this table.

    👉 **Try it:** predict which of these four run and which are rejected, then check.
    `WHERE avg("UnitPrice") > 20`; `HAVING avg("UnitPrice") > 20`;
    `WHERE "UnitPrice" > 20`; `HAVING "CategoryID" > 4`.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Sorting by an alias

    §8 sorted by `doubled`, a name that exists nowhere in `products`. It works because
    `SELECT` is step 5 and `ORDER BY` is step 6, so by the time the sort runs the column
    has been computed and named.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ShipCountry",
               count(*) AS n_orders,
               round(avg("Freight"), 2) AS avg_freight
        FROM orders
        GROUP BY "ShipCountry"
        HAVING count(*) > 500
        ORDER BY avg_freight DESC
        LIMIT 5
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `ORDER BY avg_freight` sorts on a name that `SELECT` invented two lines earlier. That
    is the whole statement, in run order: read `orders`, group by country, drop the
    countries under 500 orders, compute the two aggregate columns, sort by one of them, and
    keep the top 5.

    👉 **Try it:** take that query and replace `ORDER BY avg_freight` with
    `ORDER BY round(avg("Freight"), 2)`. Confirm you get the same five rows, then state which
    version you would rather read in six months.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### An alias in `WHERE`

    The textbook demonstration of the run order is that a `SELECT` alias cannot be used in
    `WHERE`, because `WHERE` runs first. DuckDB accepts it anyway.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT "ProductName", round("UnitPrice" * 2, 2) AS doubled
        FROM products
        WHERE doubled > 100
        ORDER BY doubled DESC
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Seven rows come back and nothing complains. DuckDB resolves the name against the
    `SELECT` list as a convenience and substitutes the expression, so the query it actually runs is
    `WHERE round("UnitPrice" * 2, 2) > 100`.

    **The trap.** That is a DuckDB feature, and the run order is what the standard says, so
    an engine that follows the standard answers this query with an unknown-column error.
    Repeat the expression in `WHERE`, or wrap the query in another one and filter outside,
    which is Chapter C §21.

    The run order is still the truth. What changed is that DuckDB rewrites your query
    before running it, and a convenience that only exists in one engine is a poor thing to
    learn the rule from.

    👉 **Try it:** run the query above, then run it again with `WHERE doubled > 100`
    replaced by `HAVING doubled > 100` and read what happens. Then ask an agent whether
    your own database engine accepts an alias in `WHERE`, and test whatever it tells you.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `LIMIT` runs last

    `LIMIT 5` on a grouped and sorted query gives the top five groups. It does not read
    five rows and then group them.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT date_trunc('year', "OrderDate") AS yr,
               count(*) AS n_orders,
               sum(CASE WHEN "ShippedDate" > "RequiredDate" THEN 1 ELSE 0 END) AS late,
               round(100.0 * sum(CASE WHEN "ShippedDate" > "RequiredDate" THEN 1 ELSE 0 END)
                     / count(*), 1) AS pct_late
        FROM orders
        WHERE "OrderDate" >= DATE '2016-01-01' AND "OrderDate" < DATE '2019-01-01'
        GROUP BY yr
        HAVING count(*) > 1000
        ORDER BY pct_late DESC
        LIMIT 3
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Every clause in this chapter is in that query. In run order: read `orders`, keep the
    three years 2016 to 2018, group them by year, drop any year under 1,000 orders, compute
    the count and the two `CASE` columns, sort by the percentage, and take three.

    The worst of the three years was 2016 at 24.6 percent late, and the best was 2017 at
    19.8. The
    `WHERE` uses the half-open form from §12, the `CASE` counts a condition the way §11
    does, the `HAVING` is a fact about a group and the `pct_late` in `ORDER BY` is an alias
    that exists because `SELECT` ran first.

    👉 **Try it:** delete one clause at a time from that query and predict what each
    deletion does before you run it. Start with `HAVING`, then `LIMIT`, then `ORDER BY`,
    then `WHERE`.

    ### ✔ Check your understanding

    1. Name the clause you move to turn the written order into the run order, and
       explain what its move makes possible.
    2. `WHERE count(*) > 8` and `HAVING count(*) > 8`. One is rejected. Answer using step
       numbers, and without the word "rule".
    3. `ORDER BY avg_freight` works on an alias. Which step made that name exist, and which
       step used it?
    4. A colleague's query works in DuckDB and fails on another engine with "column doubled
       does not exist". What did they write, and what is the fix?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## Chapter challenge

    Nothing here is due. Each section above teaches one clause, and a real question needs
    most of them at once.

    **Answer this, from `orders` alone: how late are shipments, and is it getting worse?**

    §11 already has the headline. Of 16,282 orders, 3,755 shipped after the date they were
    required, which is 23.1 percent, and the yearly figure has moved between 19.8 and 25.1
    percent across 12 years. Those two sentences are the starting point. Neither of them
    says whether the trend is real.

    **Done looks like** one query per claim, each with its output visible, and one page of
    prose. The prose states what you found, names the three decisions below and says which
    way you went on each, and carries a **How I know these numbers are right** section with
    at least one independent check. A check is a second query that would disagree if the
    first one were wrong. `count(*)` against the sum of your groups is the cheapest one and
    it is not the only one you need.

    **Three decisions are yours, and no query will make them.**

    1. **What counts as late.** `"ShippedDate" > "RequiredDate"` is one answer. Shipped more
       than seven days after the order is another, and it measures something different.
       Write down which question your definition answers.
    2. **What you do with the 21.** They were placed and never shipped. Late, excluded, or
       their own category, and each choice moves the percentage by a different amount. State
       which and by how much.
    3. **What a year is.** `date_part('year', "OrderDate")` groups by when the order was
       placed. Grouping by `"ShippedDate"` puts an order placed in December and shipped in
       January into the next year. Both are defensible and they give different trends.

    **Going further.** Break the number down by something that could explain it: the
    shipper, the destination country, the order size, the month of the year. If one
    breakdown moves the picture, get the same number two ways and see whether it survives.
    Then look at whether the 830 midnight orders behave differently from the other 15,452,
    because a block of rows that came from a different source is the sort of thing that
    produces a trend nobody in the business would recognize. Write the sentence you would
    put in front of an operations manager, and then write the one sentence they would use to push
    back on you.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 🤖 Ask your agent

    Two different uses, and the second one is the one this course grades.

    ### Ask it about what this chapter left out

    - This chapter never used `EXISTS`, `ANY` or `ALL`. Ask what each of them does and why
      a `WHERE ... IN (...)` with a list of values is a different thing from
      `WHERE ... IN (SELECT ...)`.
    - Ask what `QUALIFY` does in DuckDB, and how it relates to the run order in §16.
    - Ask why `NULL` behaves the way it does, using the phrase "three-valued logic". Then
      ask it what `WHERE NOT ("Country" = 'USA')` does to rows where `Country` is `NULL`.
    - Ask which of `date_trunc`, `date_part`, `date_diff` and `strftime` exist in SQL
      Server, PostgreSQL and SQLite, and what each is called there. Check two of its
      answers against those engines' own documentation.
    - Ask what an index is, and why `WHERE date_part('year', "OrderDate") = 2016` cannot
      use one while `WHERE "OrderDate" >= DATE '2016-01-01' AND "OrderDate" < DATE
      '2017-01-01'` can.

    Test whatever comes back. Run it.

    ### Watch what it decides without telling you

    Give it this, exactly:

    > *"Write me a SQL query showing monthly order volume from the Northwind orders table."*

    Then read what came back and answer five questions.

    1. **Did it write `BETWEEN` or a half-open range?** If there is no date filter at all,
       that is also a decision.
    2. **Did it group on the raw `OrderDate` or truncate it first?** Run it. A result with
       15,932 rows in it is the §12 failure.
    3. **What did it assume the columns are called?** Northwind exists in a dozen versions.
       Check every column name it used against `DESCRIBE orders`.
    4. **What years did it write into the query?** Anything from 1996 to 1998 came from its
       training data and returns zero rows here.
    5. **Did it handle the rows where `ShippedDate` is `NULL`?** It was not asked to, and
       whether it did tells you something about what else it did without being asked.

    You asked for one query and got half a dozen decisions, and each one is a claim about
    data it has never seen.

    👉 **Try it:** send the same sentence twice, in two separate conversations, and diff the
    two queries. Then send it a third time with one extra sentence saying the date columns
    are timestamps and the data runs from 2012 to 2023, and see which of its assumptions
    survive.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## Chapter B checklist

    - [ ] I can write a `SELECT` that names its columns and quote a name with a space in it
    - [ ] I can write a `WHERE` with `AND` and `OR` in it and explain why the parentheses are
          there
    - [ ] I can explain what `LIMIT` means when there is no `ORDER BY` above it
    - [ ] I can tell `IS NULL` from `= NULL` and state what the second one returns
    - [ ] I can explain why a filter and its opposite do not add up to the whole table
    - [ ] I can explain what `DISTINCT` does to `NULL` and why `count(DISTINCT x)` disagrees
    - [ ] I can order the branches of a `CASE` so that none of them is unreachable
    - [ ] I can count a condition with `sum(CASE WHEN ... THEN 1 ELSE 0 END)` and state where
          the missing values went
    - [ ] I can write a date range that does not lose the last day of the month
    - [ ] I can explain what `date_diff('day', ...)` is counting
    - [ ] I can tell `count(*)` from `count(column)` and explain what the gap between them is
    - [ ] I can group a table and check that the group counts add back up to the row count
    - [ ] I can decide whether a condition belongs in `WHERE` or in `HAVING` and explain why
    - [ ] I can read the run order off the seven clauses and use it to explain an error
          message

    **Part C is more than one table.** It covers `JOIN`, what a `LEFT JOIN` preserves, and
    the case where joining two tables gives you more rows than either of them started with.
    """)
    return


if __name__ == "__main__":
    app.run()