Test Yourself!

#!/bin/bash
# check.sh: run both question files (BookNest's tables in l2-pg, and booknest.duckdb).
T="/mnt/d/Books/Data Engineering/demos/ch04/test"
DUCK="/home/dev/v7-l2/tools/duckdb-1.5.5/duckdb -readonly booknest.duckdb -list"
docker exec -i l2-pg psql -U postgres -d booknest -X -q -At -F ' | ' < "$T/questions-pg.sql"
cd /home/dev/v7-l2/ch04 && $DUCK -noheader -separator ' | ' < "$T/questions-duck.sql"
if [ "${1:-}" = fixes ]; then                    # the fixes quoted in the answers
  docker exec -i l2-pg psql -U postgres -d booknest -X -q -At -F ' | ' < "$T/fixes-pg.sql"
  $DUCK -noheader -separator ' | ' < "$T/fixes-duck.sql"
fi

-- Run in PostgreSQL 18 on BookNest's sample data: psql -At -F ' | ' -f questions-pg.sql
-- 1. Coupons, with a grand total. How many rows show a NULL coupon, and how do you tell
--    them apart? (Section 4.3.4)
SELECT 1, coupon, count(*) FROM orders GROUP BY ROLLUP (coupon) ORDER BY 3;
-- 2. "Average delivered order value", written two ways. Equal? (Section 4.3.6)
SELECT 2, round(avg(CASE WHEN status = 'delivered' THEN total ELSE 0 END), 2),
          round(avg(total) FILTER (WHERE status = 'delivered'), 2) FROM orders;
-- 3. A running total over a date column with a tie on day 2. (Section 4.3.7)
SELECT 3, d, x, sum(x) OVER (ORDER BY d)
FROM (VALUES (1, 10), (2, 20), (2, 30), (3, 40)) AS t(d, x);
-- 4. Customers who ordered in 2025: the sum of the monthly counts, and the yearly count.
--    (Section 4.9.3)
SELECT 4, (SELECT sum(n) FROM (SELECT count(DISTINCT customer_id) AS n FROM orders
             WHERE order_ts < '2026-01-01' GROUP BY date_trunc('month', order_ts)) m),
          (SELECT count(DISTINCT customer_id) FROM orders WHERE order_ts < '2026-01-01');
-- 5. Total revenue, from orders alone and after joining the order lines. (Section 4.9.2)
SELECT 5, (SELECT sum(total) FROM orders),
          (SELECT sum(o.total) FROM orders o JOIN order_items i USING (order_id));
-- 6. The "highest" coupon code, top-1 style. Compare with DuckDB. (Sections 4.4.2 and 4.8.6)
SELECT 6, coupon FROM orders ORDER BY coupon DESC LIMIT 1;
-- 8. A materialized view, then a new order. What does the view say? (Section 4.5.1)
BEGIN;
CREATE MATERIALIZED VIEW q8 AS SELECT count(*) AS n FROM orders;
INSERT INTO orders VALUES (100001, 1, now(), 'web', 'paid', 'USD', NULL, 0, 0);
SELECT 8, n FROM q8;
ROLLBACK;
-- 9. A table whose only policy admits nobody, read by its (non-superuser) owner, first
--    with RLS enabled, then forced. (Section 4.16.1)
BEGIN;
CREATE ROLE q9_owner;
GRANT CREATE ON SCHEMA public TO q9_owner;
SET ROLE q9_owner;
CREATE TABLE q9 AS SELECT g FROM generate_series(1, 100) g;
ALTER TABLE q9 ENABLE ROW LEVEL SECURITY;
CREATE POLICY nobody ON q9 USING (false);
SELECT 9, count(*) FROM q9;
ALTER TABLE q9 FORCE ROW LEVEL SECURITY;
SELECT 9, count(*) FROM q9;
ROLLBACK;
-- 10. A Type 2 customer dimension joined to two sales, by natural key only and by
--     validity range. (Sections 4.10.2 and 4.10.6)
WITH dim (customer_key, customer_id, country, valid_from, valid_to) AS (
  VALUES (1, 1, 'AU', date '1900-01-01', date '2026-03-01'),
         (5001, 1, 'GB', date '2026-03-01', date '9999-12-31')),
f (order_date, customer_id, amount) AS (
  VALUES (date '2025-05-01', 1, 10.00), (date '2026-04-01', 1, 20.00))
SELECT 10, (SELECT sum(amount) FROM f JOIN dim USING (customer_id)),
           (SELECT sum(amount) FROM f JOIN dim ON dim.customer_id = f.customer_id
              AND f.order_date >= dim.valid_from AND f.order_date < dim.valid_to);

-- Run in the DuckDB 1.5.5 CLI on booknest.duckdb, without setting a time zone.
-- 6. The "highest" coupon code, top-1 style. Compare with PostgreSQL. (Sections 4.4.2, 4.8.6)
SELECT 6, coupon FROM orders ORDER BY coupon DESC LIMIT 1;
-- 7. An order placed at 20:00 UTC on 30 June. Which day is it? (Sections 4.6 and 4.7)
SELECT 7, CAST(TIMESTAMPTZ '2026-06-30 20:00:00+00' AS DATE), current_setting('TimeZone');