Which Channel Converts Best?

Kindled is about to renew its advertising contracts, and marketing wants to move budget towards whatever is bringing in buyers rather than whatever is bringing in signups. The two are not the same thing, and the difference between them is the entire argument.

Working it out means crossing two tables. The channel that brought somebody in is recorded once, on the user record. Whether they ever bought is in the event log, one row per action. And a buyer who came back three times is still one buyer, so rows are the wrong thing to count.

users — one row per signed-up person. source is the channel that brought them in.

events — one row per action a user took. event_type is the kind of action.

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

Task: Write a query returning two columns, source and num_purchasers, with one row per channel, counting how many separate people from that channel have at least one purchase event. Somebody who bought several times counts once. A channel that brought in signups but no buyers produces no row at all rather than a row reading zero. Rows come back with the strongest channel first, ties settled on source alphabetically.

Example output — shape only, on invented channels; the two channels level on four buyers show how that tie is settled.