Conversion Rate by Channel — Solution & ExplanationProblems / Conversion Rate by Channel / EditorialApproach

Aggregate each table separately. One CTE counts visits per channel, another counts signups per channel. This fixes the grain mismatch before any join happens.

LEFT JOIN from visits. Every channel with traffic must appear, even the one with no signups — a LEFT JOIN keeps it with NULL signup counts, and an inner join would silently drop it.

COALESCE and divide. COALESCE(signups, 0) makes both the count column and the percentage well-defined; * 100.0 forces float division; round to 2 decimals.

Key InsightThe classic blunder here is the join-then-count fan-out: visits JOIN signups USING (channel) produces visits × signups rows per channel, so both counts come out inflated (and COUNT(DISTINCT ...) only partially rescues it). Whenever two event tables share only a dimension, the safe pattern is aggregate-each-side-then-join — the join then runs on one row per key and nothing can multiply.Hints

The two tables have different grains and no shared key at the row level — the only sane join key is the channel, and only after each side is collapsed to one row per channel.

Joining visits to signups before aggregating cross-multiplies every visit with every signup in the same channel. Aggregate first, join second.

A LEFT JOIN from the visits side keeps the zero-signup channel; COALESCE(signups, 0) turns its NULL into 0 so the percentage arithmetic yields 0.00 instead of NULL.

Solve Conversion Rate by Channel yourself →