# Chapter 11 · Draft exercise solutions

Constructed examples. These are checkable teaching answers, not author-approved solutions.

1. The case-level rate is $(1+0+1)/3=2/3$. A has three messages, B one and C two, so the joined-row rate is $(3+0+2)/(3+1+2)=5/6$. The units are cases and case-message pairs, respectively.
2. If the fourth eligible and mature case's binary outcome is unknown, the numerator is either two or three out of four: 50%–75%. If it is not yet mature, a prespecified mature-cohort estimand would have a different denominator.
3. `WITH m AS (SELECT case_id, COUNT(*) n FROM messages GROUP BY case_id) SELECT c.case_id, COALESCE(m.n,0) FROM cases c LEFT JOIN m USING(case_id) ORDER BY c.case_id;` The grouping makes `m` unique at case grain, so it cannot multiply case rows.
4. `LEFT JOIN messages m ON c.case_id=m.case_id AND m.message_id='A1'` keeps all four cases. Moving `m.message_id='A1'` to `WHERE` removes the three cases with no such matched message. The former asks about all cases and a particular match; the latter selects cases having that match.
5. Late-order rate is $(1+4+0+3+1+12)/(10+20+5+15+10+40)=21/100=21\%$. The mean of six batch rates is $(10+20+0+20+10+30)/6=15\%$. The first weights orders equally; the second weights batches equally.
6. North: $8/45=17.78\%$; South: $13/55=23.64\%$. Check $45+55=100$ and $8+13=21$. Their simple regional mean would answer a different question.
7. With B07, total is 110 orders and 23 late, or $23/110=20.91\%$. North is 55 orders and 10 late, or $10/55=18.18\%$. Refresh the pivot and check the source includes B07; a visible table row is no guarantee the pivot has ingested it.
8. Four unmatched cases could arise from missing lookup keys, inconsistent spelling/type, or a deliberate exclusion coded by join condition. Count left-side keys lacking a match with a left join and `WHERE right.key IS NULL`; compare distinct key sets and inspect the four records. Also test duplicate keys on the right because an inner join could lose some rows while multiplying others and coincidentally still return 46.
9. A seven-row window averages seven observations. With two missing dates, those observations may span nine calendar days or longer. Create a complete calendar, distinguish zero activity from absent observation, and specify the intended time window.
10. If the outcome window is open, mark the batch pending and exclude it from a final-rate cohort under a declared maturity rule. If reporting is provisional, give a bound using zero to twelve late orders for that batch and label it. Filling blank `Late` with zero silently asserts all twelve were on time and is indefensible without evidence.
11. Blocking: unique Batch ID, Orders positive integer, `0 ≤ Late ≤ Orders` when complete. Warnings: a large period shift in late rate and a new/unrecognised region label. A high rate may be a genuine operational event; it is not structurally invalid and should not be deleted automatically.
12. Record input four case rows/four unique case IDs, six message rows/six unique message IDs, one unmatched case D, seven rows after raw left join including D's NULL message, and four rows after aggregating messages to case grain. Never sum the case-level `resolved` attribute over raw case-message rows as though it were a case count; the raw join yields five resolved-labelled message rows rather than two resolved cases.
