-- Off the Clock, Volume One - Tsumugi (plates 01-07)
-- Runnable companion to the printed margin notes. TsundereSQL, Orphan Rows.
--
-- HOW TO RUN
--   1. Install the DuckDB CLI (https://duckdb.org/docs/installation) and run:
--        duckdb -init tsumugi-companion.sql
--      or, from inside a duckdb shell:
--        .read tsumugi-companion.sql
--   2. No install: open the DuckDB browser shell at https://shell.duckdb.org and
--      paste this file in, one plate section at a time.
--
-- Every statement below is copied from the fixture bank that produced the numbers
-- printed in the book. They were executed against DuckDB 1.5.3-r.3 on 2026-09-05;
-- the result of each query is written under it as a comment, so you can predict
-- first and check yourself. Sections use CREATE OR REPLACE TABLE and may be run in
-- any order, or the whole file top to bottom.

-- --------------------------------------------------------------------------
-- PLATE 01 - Plum Robe Field Notes
-- --------------------------------------------------------------------------
-- A right-table filter in WHERE quietly turns your LEFT JOIN into an INNER JOIN

-- setup
CREATE OR REPLACE TABLE customers (
  customer_id INTEGER,
  name        VARCHAR,
  region      VARCHAR
);
INSERT INTO customers VALUES
  (1, 'Aiko',   'APAC'),
  (2, 'Bruno',  'EMEA'),
  (3, 'Carla',  'AMER'),
  (4, 'Dmitri', 'EMEA');

CREATE OR REPLACE TABLE orders (
  order_id    INTEGER,
  customer_id INTEGER,
  status      VARCHAR,
  order_total DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (101, 1, 'shipped',   120.00),
  (102, 1, 'cancelled',  80.00),
  (103, 2, 'cancelled',  45.00),
  (104, 3, 'shipped',   200.00);

-- PREDICT BEFORE YOU RUN THE NEXT STATEMENT:
--   Four customers, a LEFT JOIN to orders, and the filter o.status = 'shipped' in the WHERE clause.
--   How many rows come back? Answer: 2 (Aiko 1, Carla 1).
--   Move the same filter into the ON and you get 4 rows, with shipped_orders = 0 for Bruno and Dmitri.

-- wrong: filter in WHERE
SELECT c.name, COUNT(o.order_id) AS shipped_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped'
GROUP BY c.name
ORDER BY c.name;
-- returns:
--   name   shipped_orders
--   -----  --------------
--   Aiko   1
--   Carla  1
--   (2 rows)

-- right: filter in ON
SELECT c.name, COUNT(o.order_id) AS shipped_orders
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'shipped'
GROUP BY c.name
ORDER BY c.name;
-- returns:
--   name    shipped_orders
--   ------  --------------
--   Aiko    1
--   Bruno   0
--   Carla   1
--   Dmitri  0
--   (4 rows)

-- also right: WHERE tolerant of NULL
SELECT c.name, COUNT(o.order_id) AS shipped_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped' OR o.status IS NULL
GROUP BY c.name
ORDER BY c.name;
-- returns:
--   name    shipped_orders
--   ------  --------------
--   Aiko    1
--   Carla   1
--   Dmitri  0
--   (3 rows)

-- mechanism: A LEFT JOIN fills unmatched right-side columns with NULL and the WHERE
-- clause runs after the join, so any predicate on a right-side column that is not
-- NULL-tolerant deletes exactly the rows the outer join existed to keep.

-- --------------------------------------------------------------------------
-- PLATE 02 - Poolside Field Notes
-- --------------------------------------------------------------------------
-- One NULL in the subquery makes NOT IN return zero rows

-- setup
CREATE OR REPLACE TABLE customers (
  customer_id INTEGER,
  name        VARCHAR
);
INSERT INTO customers VALUES
  (1, 'Aiko'),
  (2, 'Bruno'),
  (3, 'Carla'),
  (4, 'Dmitri');

-- customer_id is NULL for guest checkout (order 204).
CREATE OR REPLACE TABLE orders (
  order_id    INTEGER,
  customer_id INTEGER,
  order_total DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (201, 1,    120.00),
  (202, 1,     80.00),
  (203, 2,     45.00),
  (204, NULL,  30.00);

-- PREDICT BEFORE YOU RUN THE NEXT STATEMENT:
--   Four customers, four orders, one of which is a guest checkout with customer_id NULL.
--   How many customers does NOT IN report as having never ordered? Answer: 0 rows.
--   The correct answer is 2 (Carla, Dmitri), which NOT EXISTS returns.

-- wrong: NOT IN
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o)
ORDER BY c.customer_id;
-- returns:
--   customer_id  name
--   -----------  ----
--   (0 rows)

-- right: NOT EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;
-- returns:
--   customer_id  name
--   -----------  ------
--   3            Carla
--   4            Dmitri
--   (2 rows)

-- also right: NOT IN with the NULL filtered out
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id NOT IN (
  SELECT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL
)
ORDER BY c.customer_id;
-- returns:
--   customer_id  name
--   -----------  ------
--   3            Carla
--   4            Dmitri
--   (2 rows)

-- the three-valued logic behind it
SELECT
  3 IN (1, 2, NULL)     AS in_result,
  3 NOT IN (1, 2, NULL) AS not_in_result,
  (3 NOT IN (1, 2, NULL)) IS NULL AS not_in_is_null;
-- returns:
--   in_result  not_in_result  not_in_is_null
--   ---------  -------------  --------------
--   NULL       NULL           true
--   (1 row)

-- mechanism: NOT IN is evaluated as a chain of <> comparisons under three-valued logic,
-- so one NULL on the right makes every comparison UNKNOWN and the predicate can never
-- evaluate to TRUE.

-- --------------------------------------------------------------------------
-- PLATE 03 - Tsundere Couture
-- --------------------------------------------------------------------------
-- SUM(order_total) after joining line items double-counts revenue

-- setup
CREATE OR REPLACE TABLE orders (
  order_id    INTEGER,
  customer_id INTEGER,
  order_total DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (101, 1, 120.00),
  (102, 2,  80.00),
  (103, 3,  45.00);

CREATE OR REPLACE TABLE line_items (
  line_item_id INTEGER,
  order_id     INTEGER,
  sku          VARCHAR,
  qty          INTEGER,
  unit_price   DECIMAL(10,2)
);
INSERT INTO line_items VALUES
  (1, 101, 'MUG-01',  2, 30.00),
  (2, 101, 'TEE-04',  1, 60.00),
  (3, 102, 'CAP-09',  1, 80.00),
  (4, 103, 'PIN-02',  3,  5.00),
  (5, 103, 'PIN-07',  1, 10.00),
  (6, 103, 'STK-11',  4,  5.00);

-- PREDICT BEFORE YOU RUN THE NEXT STATEMENT:
--   Three orders totalling 245.00, joined to six line items.
--   What does SUM(o.order_total) report? Answer: 455.00 — 86% too high.
--   Order 101 (120.00) is counted twice and order 103 (45.00) three times.

-- baseline: true order revenue
SELECT SUM(order_total) AS revenue FROM orders;
-- returns:
--   revenue
--   -------
--   245.00
--   (1 row)

-- wrong: SUM after joining line items
SELECT SUM(o.order_total) AS revenue
FROM orders o
JOIN line_items li ON li.order_id = o.order_id;
-- returns:
--   revenue
--   -------
--   455.00
--   (1 row)

-- why: the join multiplied the order rows
SELECT o.order_id, o.order_total, COUNT(*) AS row_copies,
       o.order_total * COUNT(*) AS counted_as
FROM orders o
JOIN line_items li ON li.order_id = o.order_id
GROUP BY o.order_id, o.order_total
ORDER BY o.order_id;
-- returns:
--   order_id  order_total  row_copies  counted_as
--   --------  -----------  ----------  ----------
--   101       120.00       2           240.00
--   102       80.00        1           80.00
--   103       45.00        3           135.00
--   (3 rows)

-- right: aggregate the line items instead
SELECT SUM(li.qty * li.unit_price) AS revenue
FROM orders o
JOIN line_items li ON li.order_id = o.order_id;
-- returns:
--   revenue
--   -------
--   245.00
--   (1 row)

-- right: aggregate before joining
SELECT SUM(o.order_total) AS revenue
FROM orders o
WHERE EXISTS (SELECT 1 FROM line_items li WHERE li.order_id = o.order_id);
-- returns:
--   revenue
--   -------
--   245.00
--   (1 row)

-- mechanism: A one-to-many join repeats each parent row once per child row, so an
-- aggregate over a parent-grain column counts that value once per child instead of once
-- per parent.

-- --------------------------------------------------------------------------
-- PLATE 04 - Corrects the Join
-- --------------------------------------------------------------------------
-- COUNT(*) counts join rows; COUNT(DISTINCT order_id) counts orders

-- setup
CREATE OR REPLACE TABLE customers (
  customer_id INTEGER,
  name        VARCHAR
);
INSERT INTO customers VALUES
  (1, 'Aiko'),
  (2, 'Bruno'),
  (3, 'Carla');

CREATE OR REPLACE TABLE orders (
  order_id    INTEGER,
  customer_id INTEGER
);
INSERT INTO orders VALUES
  (101, 1),
  (102, 1),
  (103, 2);

CREATE OR REPLACE TABLE line_items (
  line_item_id INTEGER,
  order_id     INTEGER,
  sku          VARCHAR
);
INSERT INTO line_items VALUES
  (1, 101, 'MUG-01'),
  (2, 101, 'TEE-04'),
  (3, 101, 'CAP-09'),
  (4, 102, 'PIN-02'),
  (5, 102, 'STK-11'),
  (6, 103, 'BAG-03');

-- PREDICT BEFORE YOU RUN THE NEXT STATEMENT:
--   customers LEFT JOIN orders LEFT JOIN line_items, grouped by customer.
--   Aiko has 2 orders and 5 line items.
--   What do the three counters say for her? Answer: COUNT(*) = 5, COUNT(o.order_id) = 5, COUNT(DISTINCT o.order_id) = 2.
--   And Carla, who has no orders at all: COUNT(*) = 1 but COUNT(o.order_id) = 0.

-- all three counters side by side
SELECT c.name,
       COUNT(*)                    AS count_star,
       COUNT(o.order_id)           AS count_order_id,
       COUNT(DISTINCT o.order_id)  AS count_distinct_orders
FROM customers c
LEFT JOIN orders     o  ON o.customer_id = c.customer_id
LEFT JOIN line_items li ON li.order_id   = o.order_id
GROUP BY c.name
ORDER BY c.name;
-- returns:
--   name   count_star  count_order_id  count_distinct_orders
--   -----  ----------  --------------  ---------------------
--   Aiko   5           5               2
--   Bruno  1           1               1
--   Carla  1           0               0
--   (3 rows)

-- right: count orders and items with FILTER-free separate grains
SELECT c.name,
       COUNT(DISTINCT o.order_id)     AS orders,
       COUNT(li.line_item_id)         AS line_items
FROM customers c
LEFT JOIN orders     o  ON o.customer_id = c.customer_id
LEFT JOIN line_items li ON li.order_id   = o.order_id
GROUP BY c.name
ORDER BY c.name;
-- returns:
--   name   orders  line_items
--   -----  ------  ----------
--   Aiko   2       5
--   Bruno  1       1
--   Carla  0       0
--   (3 rows)

-- mechanism: COUNT(*) counts rows of the joined result, COUNT(col) counts non-NULL
-- values of that column, and COUNT(DISTINCT col) counts distinct non-NULL values — three
-- different grains out of one FROM clause.

-- --------------------------------------------------------------------------
-- PLATE 05 - Abyssal Query Observatory
-- --------------------------------------------------------------------------
-- Two payments on the same day get the same running total under RANGE

-- setup
CREATE OR REPLACE TABLE payments (
  payment_id INTEGER,
  paid_on    DATE,
  amount     DECIMAL(10,2)
);
INSERT INTO payments VALUES
  (1, DATE '2026-03-01', 100.00),
  (2, DATE '2026-03-02',  50.00),
  (3, DATE '2026-03-02',  75.00),
  (4, DATE '2026-03-03',  20.00);

-- PREDICT BEFORE YOU RUN THE NEXT STATEMENT:
--   Four payments; two of them share the date 2026-03-02.
--   Under SUM(amount) OVER (ORDER BY paid_on) with no frame clause, what is the running total on the first of those two rows (50.00)? Answer: 225.00, not 150.00.
--   ROWS framing gives 150.00.

-- wrong-looking-right: no frame clause (defaults to RANGE)
SELECT payment_id, paid_on, amount,
       SUM(amount) OVER (ORDER BY paid_on) AS running_total
FROM payments
ORDER BY payment_id;
-- returns:
--   payment_id  paid_on     amount  running_total
--   ----------  ----------  ------  -------------
--   1           2026-03-01  100.00  100.00
--   2           2026-03-02  50.00   225.00
--   3           2026-03-02  75.00   225.00
--   4           2026-03-03  20.00   245.00
--   (4 rows)

-- explicit RANGE: identical to the default
SELECT payment_id, paid_on, amount,
       SUM(amount) OVER (
         ORDER BY paid_on
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM payments
ORDER BY payment_id;
-- returns:
--   payment_id  paid_on     amount  running_total
--   ----------  ----------  ------  -------------
--   1           2026-03-01  100.00  100.00
--   2           2026-03-02  50.00   225.00
--   3           2026-03-02  75.00   225.00
--   4           2026-03-03  20.00   245.00
--   (4 rows)

-- right: ROWS, one row at a time
SELECT payment_id, paid_on, amount,
       SUM(amount) OVER (
         ORDER BY paid_on, payment_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM payments
ORDER BY payment_id;
-- returns:
--   payment_id  paid_on     amount  running_total
--   ----------  ----------  ------  -------------
--   1           2026-03-01  100.00  100.00
--   2           2026-03-02  50.00   150.00
--   3           2026-03-02  75.00   225.00
--   4           2026-03-03  20.00   245.00
--   (4 rows)

-- mechanism: With an ORDER BY and no frame clause the default frame is RANGE BETWEEN
-- UNBOUNDED PRECEDING AND CURRENT ROW, and under RANGE "current row" means the current
-- row plus all of its peers — every row with the same ORDER BY value.

-- --------------------------------------------------------------------------
-- PLATE 06 - Midnight Proof Express
-- --------------------------------------------------------------------------
-- BETWEEN on a timestamp drops almost a whole final day

-- setup
CREATE OR REPLACE TABLE orders (
  order_id    INTEGER,
  placed_at   TIMESTAMP,
  order_total DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (101, TIMESTAMP '2026-12-01 09:00:00', 120.00),
  (102, TIMESTAMP '2026-12-15 12:30:00',  80.00),
  (103, TIMESTAMP '2026-12-31 00:00:00',  45.00),
  (104, TIMESTAMP '2026-12-31 23:45:10', 500.00),
  (105, TIMESTAMP '2027-01-01 00:00:00',  10.00);

-- PREDICT BEFORE YOU RUN THE NEXT STATEMENT:
--   Five orders, four of them in December 2026.
--   WHERE placed_at BETWEEN DATE '2026-12-01' AND DATE '2026-12-31' reports how many, and how much revenue? Answer: 3 orders, 245.00.
--   The truth is 4 orders and 745.00 — the missing one is a 500.00 order at 2026-12-31 23:45:10.

-- wrong: BETWEEN two dates
SELECT COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders
WHERE placed_at BETWEEN DATE '2026-12-01' AND DATE '2026-12-31';
-- returns:
--   orders  revenue
--   ------  -------
--   3       245.00
--   (1 row)

-- right: half-open range
SELECT COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders
WHERE placed_at >= TIMESTAMP '2026-12-01 00:00:00'
  AND placed_at <  TIMESTAMP '2027-01-01 00:00:00';
-- returns:
--   orders  revenue
--   ------  -------
--   4       745.00
--   (1 row)

-- the row BETWEEN silently drops
SELECT order_id, placed_at, order_total
FROM orders
WHERE placed_at >= TIMESTAMP '2026-12-01 00:00:00'
  AND placed_at <  TIMESTAMP '2027-01-01 00:00:00'
  AND NOT (placed_at BETWEEN DATE '2026-12-01' AND DATE '2026-12-31');
-- returns:
--   order_id  placed_at            order_total
--   --------  -------------------  -----------
--   104       2026-12-31 23:45:10  500.00
--   (1 row)

-- mechanism: BETWEEN is inclusive of both endpoints, so a date-valued upper bound really
-- means midnight at the start of that day and every timestamp later in that day falls
-- outside the range.

-- --------------------------------------------------------------------------
-- PLATE 07 - Server Cathedral Encore
-- --------------------------------------------------------------------------
-- SUM(DISTINCT x) dedupes values, not rows, and eats a real order

-- setup
-- Two different orders happen to have the same total: 120.00.
CREATE OR REPLACE TABLE orders (
  order_id    INTEGER,
  customer_id INTEGER,
  order_total DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (101, 1, 120.00),
  (102, 2, 120.00),
  (103, 3,  45.00);

CREATE OR REPLACE TABLE line_items (
  line_item_id INTEGER,
  order_id     INTEGER,
  sku          VARCHAR
);
INSERT INTO line_items VALUES
  (1, 101, 'MUG-01'),
  (2, 101, 'TEE-04'),
  (3, 102, 'CAP-09'),
  (4, 103, 'PIN-02'),
  (5, 103, 'PIN-07');

-- PREDICT BEFORE YOU RUN THE NEXT STATEMENT:
--   Three orders — 120.00, 120.00, 45.00 — joined to five line items.
--   Someone notices the fanout and writes SUM(DISTINCT o.order_total).
--   What comes out? Answer: 165.00. True revenue is 285.00; the naive join gives 450.00.

-- baseline: true order revenue
SELECT SUM(order_total) AS revenue FROM orders;
-- returns:
--   revenue
--   -------
--   285.00
--   (1 row)

-- wrong #1: plain SUM after the join (fanned out)
SELECT SUM(o.order_total) AS revenue
FROM orders o
JOIN line_items li ON li.order_id = o.order_id;
-- returns:
--   revenue
--   -------
--   450.00
--   (1 row)

-- wrong #2: SUM(DISTINCT) "fixes" it and loses an order
SELECT SUM(DISTINCT o.order_total) AS revenue
FROM orders o
JOIN line_items li ON li.order_id = o.order_id;
-- returns:
--   revenue
--   -------
--   165.00
--   (1 row)

-- right: dedupe rows by key, then sum
SELECT SUM(order_total) AS revenue
FROM (
  SELECT DISTINCT o.order_id, o.order_total
  FROM orders o
  JOIN line_items li ON li.order_id = o.order_id
) deduped;
-- returns:
--   revenue
--   -------
--   285.00
--   (1 row)

-- mechanism: DISTINCT inside an aggregate deduplicates values, not rows, so it silently
-- discards genuinely repeated measurements while appearing to repair a join fanout.

-- --------------------------------------------------------------------------
-- END
-- --------------------------------------------------------------------------
-- Play the game these came from: https://tsunderesql.itch.io/tsunderesql
