CASE & UNION: one report, many sources

READ & UNDERSTAND

Start a multi-source report with an inclusion rule for each branch. For a member activity report, sessions and registrations have member IDs; café orders include both members and anonymous walk-ins. A customer report may include walk-ins, while a member loyalty report must exclude them.

Count each branch after its filters. With UNION ALL, the total must equal the sum of those branch counts. Equality is necessary but not sufficient: wrong columns, dates or business rules can still produce the expected count.

Keep the three time meanings explicit: session_date records play, order_date records a purchase, registered_date records a signup. event_date is the scheduled event day. Use this audit method when building the official ledger yourself; the point rules and required output stay in the Canvas handout.

SELECT 'Session' AS source, COUNT(*) AS records FROM sessions UNION ALL SELECT 'Signup', COUNT(*) FROM registrations UNION ALL SELECT 'Member order', COUNT(*) FROM cafe_orders WHERE member_id IS NOT NULL ORDER BY source;

Your question

Return source and records for two café populations: Member order for non-NULL member_id, and Walk-in order for NULL member_id. Stack the counts with UNION ALL and sort by source.

Explain your evidence

If the final count reconciles, what additional evidence would you check before approving the report?

Try a query to unlock the worked-answer discussion.