Interview Question Bank · RANK with ties · SUM() OVER () before filtering
8. Whales and their share of revenue
MediumGamingQuestion #152
Concept: RANK with ties · SUM() OVER () before filteringshow
To list the top spenders and the share each brings in, total spend per person, rank them with RANK() so ties share a place, and divide each total by SUM(total) OVER (), the grand total across every row. Window functions run after WHERE, so compute that share in a CTE before filtering to the top ranks; otherwise the grand total only covers the rows you kept.
Example (different data):
WITH t AS (
SELECT author_id, SUM(copies) AS sold,
RANK() OVER (ORDER BY SUM(copies) DESC) AS r,
100.0 * SUM(copies) / SUM(SUM(copies)) OVER () AS share
FROM book_sales GROUP BY author_id
)
SELECT * FROM t WHERE r <= 5;Your task
Skyrift Arena's live-ops team is inviting its biggest spenders to a VIP program and wants to see how much of the game's revenue they bring in. Refunded purchases don't count.
Counting only purchases that weren't refunded, rank players by net_spend (highest first, ties share a rank, as with RANK) and return the players ranked 1 to 3: spend_rank, username, net_spend and revenue_share_pct (net_spend as a % of every player's combined net revenue, rounded to 1 decimal). Sort by spend_rank, then username.
Starting Postgres in your browser…
Checking your access…