Interview Question Bank · Per-player totals · LEFT JOIN · two denominators
5. ARPU vs ARPPU by platform
MediumGamingQuestion #149
Concept: Per-player totals · LEFT JOIN · two denominatorsshow
ARPU (average revenue per user) divides revenue by every user; ARPPU (per paying user) divides the same revenue by payers only. Total each user's spend first, then LEFT JOIN those totals onto the users table: COUNT(*) is then all users and COUNT(spend column) is just the payers, since COUNT skips NULLs. Averaging the raw purchase rows gives the average purchase, which is a third, different number.
Example (different data):
WITH tips AS ( SELECT driver_id, SUM(tip) AS total FROM rides GROUP BY driver_id ) SELECT d.city, ROUND(COALESCE(SUM(t.total), 0) / COUNT(*), 2) AS per_driver, ROUND(SUM(t.total) / COUNT(t.driver_id), 2) AS per_tipped_driver FROM drivers d LEFT JOIN tips t ON t.driver_id = d.id GROUP BY d.city;
Your task
Skyrift Arena's monetization team is deciding where to run a store sale and wants both ARPU and ARPPU for each platform. Refunded purchases aren't revenue.
Using only purchases that weren't refunded, return platform, players (all players on it), payers (players with at least one such purchase), revenue, arpu (revenue / players) and arppu (revenue / payers), both rounded to 2 decimals. Sort by arppu descending, then platform.
Starting Postgres in your browser…
Checking your access…