Which Channel Converts?

Kindled's marketing budget is decided by which channel produces sales, so the first table on that agenda is purchases split by how the buyer originally arrived. The buying is recorded in the activity table and the channel on the account record, so the two have to be brought together before the question can even be asked. A channel that produced no sales at all is part of the argument too.

events — one row per tracked action. user_id points at a row in users. event_type is one of signup, view_product, add_to_cart and purchase. event_date is the day the action happened. Nothing guarantees a person produces all four kinds, or any particular number of rows.

users — one row per person who has created an account. source records how they arrived: organic means they found Kindled themselves, paid_search means they clicked a bought advert, and referral means an existing customer invited them. signup_date is the day they registered.

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

Task: Write a query that returns one row per channel that produced at least one purchase, with columns source and purchases — how many purchase actions came from people who arrived through that channel. Most purchases first; channels on the same count are listed alphabetically by source.

Example output — shape only, on invented tallies. The source labels are the real stored ones; a channel that sold nothing is absent rather than zero: