Drill 8 of 12 in Gaming questions
0

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…