Each Player's First Store Purchase After Sign-up

Medium
190.

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? 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

ColumnType
player_idint
gamertagvarchar
signup_datedate

Primary key: player_id.

Gamertags are unique.

Table: Purchase

ColumnType
purchase_idint
player_idint
item_namevarchar
amount_inrint
purchased_atdatetime

Primary key: purchase_id.

One row per completed purchase, never before the player's signup date.

Example

The rows must come back in the order shown.

Player

player_idgamertagsignup_date
1TurboTanvi2025-01-10
2DesiDragon2025-01-12
3GhostArjun2025-01-12
4SpeedySimran2025-01-15

Purchase

purchase_idplayer_iditem_nameamount_inrpurchased_at
111600 Gems4992025-01-14 20:00:00
121Neon Tiger Skin7992025-01-11 22:30:00
132Season 12 Pass4492025-01-12 23:50:00
142Victory Dab992025-01-12 23:50:00
153Mystic Crate1992025-02-01 08:00:00
1622500 Gems17992025-01-20 12:00:00

Output

gamertagfirst_itemdays_to_first_purchase
DesiDragonSeason 12 Pass0
TurboTanviNeon Tiger Skin1
GhostArjunMystic Crate20
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
player_idint
gamertagvarchar
signup_datedate

Primary key: player_id.

Gamertags are unique.

Table: Purchase

ColumnType
purchase_idint
player_idint
item_namevarchar
amount_inrint
purchased_atdatetime

Primary key: purchase_id.

One row per completed purchase, never before the player's signup date.

Examples

Example 1

Player

player_idgamertagsignup_date
1TurboTanvi2025-01-10
2DesiDragon2025-01-12
3GhostArjun2025-01-12
4SpeedySimran2025-01-15

Purchase

purchase_idplayer_iditem_nameamount_inrpurchased_at
111600 Gems4992025-01-14 20:00:00
121Neon Tiger Skin7992025-01-11 22:30:00
132Season 12 Pass4492025-01-12 23:50:00
142Victory Dab992025-01-12 23:50:00
153Mystic Crate1992025-02-01 08:00:00
1622500 Gems17992025-01-20 12:00:00

Output

gamertagfirst_itemdays_to_first_purchase
DesiDragonSeason 12 Pass0
TurboTanviNeon Tiger Skin1
GhostArjunMystic Crate20

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.gamertag

Another 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.gamertag

Another 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 →