Multiple one-to-many joins and honest counts

One role can have several skills and several applications. Joining both child tables directly creates every matching skill-application combination for that role. Role 1 has two skills and two applications, so a raw join produces four rows even though neither child table contains four records for it.

The worked report needs one row per role with two independent counts. Define that output grain first. Then count skills and applications separately at role_id grain before joining the two small results back to roles.

WITH gives a name to an intermediate query result. Here applications are grouped by role_id in one named result and skills in another. Each named result has at most one row per role, so joining both cannot create a cross-product of their original child records.

LEFT JOIN retains roles without either kind of evidence. COALESCE changes an absent aggregate row to the count zero, which is a justified empty-set count here. This differs from inventing a zero salary for a missing salary observation.

The counterexample counts after joining both child tables. It reports four applications and four skills for role 1. COUNT(DISTINCT child_id) can repair some particular counts, but distinctness must match the entity and can become awkward when several measures have different grains.

Preaggregation makes each measure's contract visible. It also helps avoid incorrect sums, where a distinct value is not a safe replacement for a distinct entity. Two separate applications can legitimately have the same measured amount; a value-level DISTINCT could then undercount them.

Produce only the application count for every role. Reuse one grouped intermediate result, left join it to roles and show zero for missing matches. This deliberately removes the second child relationship so the independent task is smaller than the full worked example.

Predict counts 2, 0, 1, 1, 0, 1, 0, 0 for identities 1 through 8. Write the grouped application query first and inspect it before adding the outer join. Keeping intermediate grains visible is the key skill, not writing the shortest possible statement.

Step 1: Group applications by role_id. Step 2: Join those counts to all roles and fill absent counts with zero.

The solution returns eight role rows with the predicted application counts. The grouped child result has only four role identities, so the outer join and justified zero replacement are both necessary to retain the remaining four roles honestly.

The acceptance check includes role 1, which exposes multiplication in a naive two-child join, and roles without applications, which expose accidental inner joins. A count-only test on the final output would miss incorrect measures attached to otherwise correct identities.

Explain join inflation by drawing the two-by-two combinations for one parent. This is more persuasive than saying joins sometimes duplicate rows. Name the grain of every intermediate result and show which uniqueness property lets you combine their measures safely.

The same principle applies to orders, line items, payments and events. Do not use a raw joined row count as an entity count without proving the relationship. The following aggregation lessons make observed denominators and grouped populations explicit.

Optional stretch — after the core task works: Explain a case where SUM(DISTINCT amount) would undercount separate equal-valued events.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.