Drill 1 of 12 in Gaming questions
0

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…