Which Acquisition Channel Actually Converts?

Kindled buys traffic from three places and has been judging them by how many sign-ups each one delivers. Paid search wins that contest and has never produced a single sale. The team now wants each channel scored on what fraction of the people it brought in went on to buy.

That fraction is the awkward part, because its denominator includes people who left no trace. A channel's non-buyers are exactly the users with no purchase event, so a report built by looking at purchases alone cannot see them — and a channel whose users never buy has to come back as 0, not vanish from the report and not read 1 because the only rows left were purchases.

users — one row per user. source is the channel that brought them in.

events — one row per action. event_type is one of signup, view_product, add_to_cart or purchase.

Both tables already exist in the database — there is nothing to create or load.

Task: Write a query returning two columns, source and conversion_rate, with one row for each of the three channels. conversion_rate is the number of that channel's users who have at least one purchase event, divided by the number of users the channel brought in — a fraction between 0 and 1, not a percentage, rounded to 4 decimal places. A channel none of whose users bought anything comes back as 0 rather than being missing. Rows come back in alphabetical order of source.

Example output — shape only, on invented rates. A channel none of whose users bought would read 0, and one every user of which bought would read 1.