Channel Conversion Rates

Kindled's marketing meeting keeps confusing volume with quality. A channel that sends a thousand people and sells to five is worse than one that sends ten and sells to three, and raw purchase counts hide that completely. What settles the argument is each channel's conversion rate: how many of the people it brought in went on to buy, as a percentage of how many it brought in at all. Every channel has to appear, including one that sold nothing — a channel that fails silently is the most expensive kind.

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, with columns source and conversion_pct — the share of that channel's people who have at least one purchase, as a percentage rounded to 1 decimal place. Somebody who bought twice still counts once, and a channel whose people bought nothing reports 0 rather than disappearing. Highest rate first; equal rates are listed alphabetically by source.

Example output — shape only, on invented rates. The source labels are the real stored ones, and the bottom row is the case worth noticing: a channel nobody bought through still reports, as 0: