Investigation 1 · The join changes weight
Cases A and C are resolved; B is not. Their message counts are 3, 1 and 2.
A raw case-message join repeats the outcome label.

Investigation 2 · Which denominator?
Six constructed dispatch batches contain 100 orders and 21 late orders. Their six batch percentages average 15%; an order-weighted calculation gives 21%.

Reproduce and explain
Download the executed Python/SQLite laboratory, its constructed CSV, and the draft solutions. Rebuild the CSV as an Excel Table and PivotTable; check counts, filters and refresh.
- Why does a one-to-many join change a case-level average?
- Does a blank late count mean zero, pending, or unknown?
- What would an unrefreshed pivot show after a new row is appended?
Download the sales ledger for the spreadsheet laboratory
Inspect the work.
Read the programs, run them in your own environment, and compare the results. The captured output records the chapter’s checked practical run; changing an input may change its answer.
Python table_system_lab.py
"""Constructed Chapter 11 cases, joins and dispatch batches; Python 3 stdlib only."""
from fractions import Fraction
from pathlib import Path
import csv
import sqlite3
HERE = Path(__file__).resolve().parent
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE cases(case_id TEXT PRIMARY KEY, resolved INTEGER CHECK(resolved IN (0,1) OR resolved IS NULL));
CREATE TABLE messages(message_id TEXT PRIMARY KEY, case_id TEXT NOT NULL REFERENCES cases(case_id));
INSERT INTO cases VALUES ('A',1),('B',0),('C',1),('D',NULL);
INSERT INTO messages VALUES ('A1','A'),('A2','A'),('A3','A'),('B1','B'),('C1','C'),('C2','C');
""")
def one(query): return db.execute(query).fetchone()
case_rate = one("SELECT SUM(resolved)*1.0/COUNT(resolved) FROM cases WHERE case_id IN ('A','B','C')")[0]
joined_rate = one("SELECT SUM(c.resolved)*1.0/COUNT(c.resolved) FROM cases c JOIN messages m USING(case_id)")[0]
assert case_rate == 2/3 and joined_rate == 5/6
assert one("SELECT COUNT(*),COUNT(resolved) FROM cases") == (4,3)
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m USING(case_id)")[0] == 7
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id WHERE m.message_id IS NOT NULL")[0] == 6
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id AND m.message_id='A1'")[0] == 4
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id WHERE m.message_id='A1'")[0] == 1
batches = [
('B01','2026-01-05','North',10,1),('B02','2026-01-12','North',20,4),
('B03','2026-01-20','South',5,0),('B04','2026-02-03','North',15,3),
('B05','2026-02-10','South',10,1),('B06','2026-02-24','South',40,12)]
db.execute("CREATE TABLE dispatches(batch TEXT PRIMARY KEY, date TEXT, region TEXT, orders INTEGER CHECK(orders>0), late INTEGER CHECK(late BETWEEN 0 AND orders))")
db.executemany("INSERT INTO dispatches VALUES (?,?,?,?,?)", batches)
pooled = Fraction(sum(row[4] for row in batches),sum(row[3] for row in batches))
batch_mean = sum((Fraction(row[4],row[3]) for row in batches),Fraction())/len(batches)
assert pooled == Fraction(21,100) and batch_mean == Fraction(3,20)
regions = db.execute("SELECT region,SUM(orders),SUM(late),COUNT(*) FROM dispatches GROUP BY region ORDER BY region").fetchall()
assert regions == [('North',45,8,3),('South',55,13,3)]
with (HERE/'dispatches.csv').open('w',newline='') as file:
writer=csv.writer(file);writer.writerow(['Batch','Date','Region','Orders','Late']);writer.writerows(batches)
print('CASE RATE 2/3 =',format(case_rate,'.6f'))
print('JOINED MESSAGE-WEIGHTED RATE 5/6 =',format(joined_rate,'.6f'))
print('CASE COUNT / OBSERVED OUTCOME COUNT =',one('SELECT COUNT(*),COUNT(resolved) FROM cases'))
print('LEFT JOIN ROWS / INNER MATCHES =',7,6)
print('FILTER IN ON / FILTER IN WHERE ROWS =',4,1)
print('DISPATCH POOLED RATE =',f'{float(pooled):.2%}')
print('UNWEIGHTED BATCH MEAN =',f'{float(batch_mean):.2%}')
print('REGION orders, late, batches =',regions)
Output Checked chapter run
CASE RATE 2/3 = 0.666667
JOINED MESSAGE-WEIGHTED RATE 5/6 = 0.833333
CASE COUNT / OBSERVED OUTCOME COUNT = (4, 3)
LEFT JOIN ROWS / INNER MATCHES = 7 6
FILTER IN ON / FILTER IN WHERE ROWS = 4 1
DISPATCH POOLED RATE = 21.00%
UNWEIGHTED BATCH MEAN = 15.00%
REGION orders, late, batches = [('North', 45, 8, 3), ('South', 55, 13, 3)]