Top Spenders Query | Dataford Interview Questions

Your question is Top Spenders Query. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Ubisoft wants to identify the highest-spending players across its live games during a reporting period. Write a PostgreSQL query that ranks player spending and returns the top 5% based on purchases made from 2025-01-01 through 2025-03-31.

Requirements

Include all players in the spend population, including players with no qualifying purchases.
Sum only purchases within the specified timeframe, treating missing purchase amounts as zero.
Calculate the 95th percentile of total player spend and return players whose spend is at or above that threshold.
Return player_id, gamer_tag, and total_spent, ordered by spending descending and player ID ascending for deterministic results.

Schema

players

Column	Type	Description
player_idPK	INT	Unique player identifier
gamer_tag	VARCHAR(50)	Player display name
platform	VARCHAR(20)	Gaming platform

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
player_id	INT	Purchasing player identifier
purchased_at	TIMESTAMP	Purchase timestamp
amount	NUMERIC(10,2)	Purchase amount in USD
game_title	VARCHAR(80)	Ubisoft game associated with the purchase