Interview Question Bank · LEFT JOIN · condition in ON · COUNT(DISTINCT)
1. Paying conversion by acquisition channel
EasyGamingQuestion #145
Concept: LEFT JOIN · condition in ON · COUNT(DISTINCT)show
Conversion is the share of all players who paid, so the denominator has to include the players who never bought anything. Start from the players table and LEFT JOIN the purchases; non-payers then get NULLs on the purchase side. Put any filter on the purchases (like skipping refunds) in the ON clause: in WHERE it would also throw away the NULL rows. After the join, count players with COUNT(DISTINCT ...) so repeat buyers aren't counted twice.
Example (different data):
SELECT m.gym, COUNT(DISTINCT m.id) AS members, COUNT(DISTINCT c.member_id) AS booked, ROUND(100.0 * COUNT(DISTINCT c.member_id) / COUNT(DISTINCT m.id), 1) AS pct FROM members m LEFT JOIN class_bookings c ON c.member_id = m.id AND NOT c.cancelled GROUP BY m.gym;
Your task
Brightmoor Games runs Skyrift Arena, a free-to-play battle game. The user-acquisition team wants to know which channel brings in players who actually pay.
For each channel in gm_players, return channel, players (all installs), payers (players with at least one purchase that wasn't refunded) and conversion_pct (payers as a % of players, rounded to 1 decimal). Sort by conversion_pct descending, then channel.
Starting Postgres in your browser…
Checking your access…