Each Player's First Store Purchase After Sign-up
The monetisation team studies conversion: what does a new player buy first, and how long after signing up?
- Difficulty: Medium
- Topics: Window Functions, Dates, Joins
- Dialect: MySQL
- Problem: #190
Problem statement
The monetisation team studies conversion: what does a new player buy first, and how long after signing up? For every player who has made at least one purchase, take their earliest purchase by purchased_at; if two purchases share the same timestamp, the one with the smaller purchase_id is first.
Return gamertag, first_item and days_to_first_purchase (whole days from signup_date to the date of that purchase; 0 for the same day), ordered by days_to_first_purchase, then gamertag.
Tables
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| gamertag | varchar |
| signup_date | date |
Primary key: player_id.
Gamertags are unique.
Table: Purchase
| Column | Type |
|---|---|
| purchase_id | int |
| player_id | int |
| item_name | varchar |
| amount_inr | int |
| purchased_at | datetime |
Primary key: purchase_id.
One row per completed purchase, never before the player's signup date.
Examples
Example 1
Player
| player_id | gamertag | signup_date |
|---|---|---|
| 1 | TurboTanvi | 2025-01-10 |
| 2 | DesiDragon | 2025-01-12 |
| 3 | GhostArjun | 2025-01-12 |
| 4 | SpeedySimran | 2025-01-15 |
Purchase
| purchase_id | player_id | item_name | amount_inr | purchased_at |
|---|---|---|---|---|
| 11 | 1 | 600 Gems | 499 | 2025-01-14 20:00:00 |
| 12 | 1 | Neon Tiger Skin | 799 | 2025-01-11 22:30:00 |
| 13 | 2 | Season 12 Pass | 449 | 2025-01-12 23:50:00 |
| 14 | 2 | Victory Dab | 99 | 2025-01-12 23:50:00 |
| 15 | 3 | Mystic Crate | 199 | 2025-02-01 08:00:00 |
| 16 | 2 | 2500 Gems | 1799 | 2025-01-20 12:00:00 |
Output
| gamertag | first_item | days_to_first_purchase |
|---|---|---|
| DesiDragon | Season 12 Pass | 0 |
| TurboTanvi | Neon Tiger Skin | 1 |
| GhostArjun | Mystic Crate | 20 |
How to solve Each Player's First Store Purchase After Sign-up
"The first row per group" is a job for ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY purchased_at, purchase_id): within each player's purchases the earliest gets 1, and the second sort key settles two purchases in the same second (DesiDragon's pass and emote at 23:50) in favour of the smaller id. Unlike RANK, ROW_NUMBER never gives two rows the number 1, so exactly one purchase per player survives rn = 1.
Join to Player for the gamertag and signup date. DATEDIFF(purchased_at, signup_date) compares only the date parts, so a purchase at 23:50 on the signup day is 0 days, and TurboTanvi's skin bought the day after signing up is 1 — even though her gems were bought later and listed first.
The same row can be found without windows: keep a purchase when no other purchase of that player is earlier (NOT EXISTS, with the id tie-break written out), or when its id is the first of the player's purchases sorted the same way (a correlated LIMIT 1). Those do a lookup per purchase; the window sorts once.
Reference solution (MySQL)
WITH ranked AS (
SELECT pu.player_id, pu.item_name, pu.purchased_at,
ROW_NUMBER() OVER (PARTITION BY pu.player_id ORDER BY pu.purchased_at, pu.purchase_id) AS rn
FROM Purchase pu
)
SELECT p.gamertag, r.item_name AS first_item,
DATEDIFF(r.purchased_at, p.signup_date) AS days_to_first_purchase
FROM ranked r
JOIN Player p ON p.player_id = r.player_id
WHERE r.rn = 1
ORDER BY days_to_first_purchase, p.gamertagAnother way
SELECT p.gamertag, pu.item_name AS first_item, DATEDIFF(DATE(pu.purchased_at), p.signup_date) AS days_to_first_purchase
FROM Purchase pu JOIN Player p ON p.player_id = pu.player_id
WHERE NOT EXISTS (
SELECT 1 FROM Purchase e WHERE e.player_id = pu.player_id
AND (e.purchased_at < pu.purchased_at OR (e.purchased_at = pu.purchased_at AND e.purchase_id < pu.purchase_id))
)
ORDER BY days_to_first_purchase, p.gamertagAnother way
SELECT p.gamertag, pu.item_name AS first_item, DATEDIFF(pu.purchased_at, p.signup_date) AS days_to_first_purchase
FROM Purchase pu JOIN Player p ON p.player_id = pu.player_id
WHERE pu.purchase_id = (SELECT e.purchase_id FROM Purchase e WHERE e.player_id = pu.player_id ORDER BY e.purchased_at, e.purchase_id LIMIT 1)
ORDER BY days_to_first_purchase, p.gamertag← Guild Members Above Their Guild's Average Points · Longest Daily Login Streak per Player →