CASE & UNION: one report, many sources

READ & UNDERSTAND

A report covering every event must start with events and preserve unmatched rows. LEFT JOIN registrations through event_id, then count registrations.id. COUNT(*) would count the placeholder row for an event with no registration.

An aggregate can be the expression a CASE evaluates. In this readiness check, an event is Needs outreach if it has no registrations and Has signups otherwise. Group at event level before applying that rule.

registered_date is the day someone signs up; event_date is the day the event takes place. Likewise, paid records whether a fee is settled. A sign-up count is not attendance, and multiplying signups by fee does not automatically measure received revenue.

SELECT e.id, e.name, COUNT(r.id) AS signups FROM events AS e LEFT JOIN registrations AS r ON r.event_id = e.id GROUP BY e.id, e.name ORDER BY e.id;

Your question

Return event_id, signups and outreach for all events. outreach is Needs outreach at zero registrations, otherwise Has signups. Order by event_id.

Explain your evidence

Would an INNER JOIN reveal the event most in need of outreach? Why is a registration not proof of attendance?

Try a query to unlock the worked-answer discussion.