Project: build an auditable support-intake SQL report

Last updated: 5 Oct 20265 min read

project

Build a repeatable SQL report that keeps every eligible intake case visible while selecting one event version and exposing missing outcomes.

Create a fixture with failure cases

Use 47 eligible cases and seven ineligible records. Add duplicate status events, one exact occurrence-time tie with distinct revisions, four cases still open at cutoff, three reviewer outcomes recorded as null and one outcome whose case ID is absent from the intake table. Save both event occurrence and known-at times. The fixture should be small enough to inspect by hand but large enough to expose a wrong join or denominator.

Write a staged query

Stage one selects eligible case IDs. Stage two filters status events by the frozen known-at cutoff. Stage three ranks status versions per case with a stable tie-break. Stage four left-joins one selected outcome back to every eligible case. Stage five calculates eligible, observed, positive and unknown counts before any rate. Deterministic event selection and conditional counts must remain separate steps.

Reconcile every transition

Assert the eligible count remains 47 after the outcome join. Count duplicate raw event IDs, multiply matched cases and orphaned outcomes before computing the headline. Run a reverse anti-join for outcomes with no eligible intake record. Label open cases separately from missing reviewer decisions. Reconciliation rules should fail the report when identities unexpectedly multiply.

Publish two rates, not one

Report review coverage over all eligible cases and positive share among observed reviews, with each integer numerator and denominator. State that the observed positive share does not estimate the eligible-cohort share unless missing outcomes are addressed. Add a lower and upper eligible-cohort bound using the missing count. Include an analysis cutoff and a metric revision number in every output row.

Deliver a review packet

Provide schema, source fixture, SQL dialect, query, execution plan from a representative table size, expected result assertions and a short note explaining unresolved cases. A peer should rerun the report, alter a tie revision, and see the selected event change without changing eligible row count. Save that as a regression check.

Implementation

WITH eligible_cases(case_id) AS (
    VALUES (601), (602), (603), (604), (605)
), reviews(case_id, review_outcome) AS (
    VALUES (601, 1), (602, 0), (603, NULL), (604, 1)
), audit AS (
    SELECT intake.case_id, reviews.review_outcome
    FROM eligible_cases AS intake
    LEFT JOIN reviews ON reviews.case_id = intake.case_id
)
SELECT COUNT(*) AS eligible_count, COUNT(review_outcome) AS reviewed_count,
       SUM(CASE WHEN review_outcome = 1 THEN 1 ELSE 0 END) AS positive_count,
       SUM(CASE WHEN review_outcome IS NULL THEN 1 ELSE 0 END) AS unknown_count
FROM audit;

Performance and operating cost

A staged report over N eligible cases and M events is dominated by event sorting at O(M log M) in the worst case, plus joins at O(N + M) expected cost when hash joins are available. Store per-stage counts and selected event IDs; aggregate outputs alone cannot explain a revision.

Common Mistakes

Do not turn a left join into an inner join with a post-join WHERE filter.
Do not report the positive share without review coverage.
Do not discard tie-breaking and cutoff logic from the delivered query.