New-Player Funnel From Install to First Purchase by Platform
The growth team tracks a strict onboarding funnel: install → tutorial complete → first ranked match → first purchase.
- Difficulty: Hard
- Topics: Conditional Logic, Joins, Aggregation
- Dialect: MySQL
- Problem: #196
Problem statement
The growth team tracks a strict onboarding funnel: install → tutorial complete → first ranked match → first purchase. A player reaches a step only if they reached the previous one and the step happened strictly later than the previous step — a player who skipped the tutorial and played ranked first has not reached the ranked step, and a purchase made before the first ranked match does not count either.
For each platform with at least one install, return platform, installs, completed_tutorial, played_ranked, purchased and install_to_purchase_pct = 100 × purchased ÷ installs rounded to 2 decimal places. Order by platform.
Tables
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| platform | enum(android, ios, pc) |
| installed_at | datetime |
Primary key: player_id.
One row per install.
Table: FunnelEvent
| Column | Type |
|---|---|
| player_id | int |
| event_type | enum(tutorial_complete, first_ranked_match, first_purchase) |
| occurred_at | datetime |
Primary key: player_id, event_type.
Each milestone happens at most once per player, always after the install.
Examples
Example 1
Player
| player_id | platform | installed_at |
|---|---|---|
| 1 | android | 2025-05-01 10:00:00 |
| 2 | android | 2025-05-01 11:00:00 |
| 3 | android | 2025-05-02 09:00:00 |
| 4 | ios | 2025-05-02 12:00:00 |
| 5 | ios | 2025-05-03 08:00:00 |
| 6 | pc | 2025-05-03 20:00:00 |
FunnelEvent
| player_id | event_type | occurred_at |
|---|---|---|
| 1 | tutorial_complete | 2025-05-01 10:20:00 |
| 1 | first_ranked_match | 2025-05-02 19:00:00 |
| 1 | first_purchase | 2025-05-04 21:00:00 |
| 2 | tutorial_complete | 2025-05-01 11:30:00 |
| 2 | first_purchase | 2025-05-01 12:00:00 |
| 3 | first_ranked_match | 2025-05-02 10:00:00 |
| 3 | tutorial_complete | 2025-05-02 11:00:00 |
| 4 | tutorial_complete | 2025-05-02 12:15:00 |
| 4 | first_ranked_match | 2025-05-03 18:00:00 |
| 4 | first_purchase | 2025-05-03 18:00:00 |
Output
| platform | installs | completed_tutorial | played_ranked | purchased | install_to_purchase_pct |
|---|---|---|---|---|---|
| android | 3 | 3 | 1 | 1 | 33.33 |
| ios | 2 | 1 | 1 | 0 | 0 |
| pc | 1 | 0 | 0 | 0 | 0 |
How to solve New-Player Funnel From Install to First Purchase by Platform
First pivot: one row per player with the time of each milestone, via MAX(CASE WHEN event_type = … THEN occurred_at END) grouped by player (each milestone happens at most once, so MAX just picks it). Then turn the times into 0/1 step flags with CASE. Step 2 is ranked_at > tutorial_at: if either is NULL the comparison is unknown and CASE falls to 0, so a missing tutorial and a ranked match before the tutorial (player 3) both fail. Step 3 also requires step 2, so player 2's purchase — made without any ranked match — does not count, and player 4's purchase at the same second as the ranked match is not strictly later.
LEFT JOIN the pivot to Player so players with no events at all are installs with every flag 0, then sum the flags per platform. The conversion is 100 * purchased / installs, rounded.
The chained LEFT JOIN version encodes the funnel in the joins: each step joins to the previous step's row, with the time condition in ON, so a player who fails a step has NULLs from there on and COUNT(column) counts only those who got through. Both are a grouping over players.
Reference solution (MySQL)
WITH firsts AS (
SELECT player_id,
MAX(CASE WHEN event_type = 'tutorial_complete' THEN occurred_at END) AS tutorial_at,
MAX(CASE WHEN event_type = 'first_ranked_match' THEN occurred_at END) AS ranked_at,
MAX(CASE WHEN event_type = 'first_purchase' THEN occurred_at END) AS purchase_at
FROM FunnelEvent
GROUP BY player_id
), steps AS (
SELECT p.platform,
CASE WHEN f.tutorial_at IS NOT NULL THEN 1 ELSE 0 END AS s1,
CASE WHEN f.ranked_at > f.tutorial_at THEN 1 ELSE 0 END AS s2,
CASE WHEN f.ranked_at > f.tutorial_at AND f.purchase_at > f.ranked_at THEN 1 ELSE 0 END AS s3
FROM Player p
LEFT JOIN firsts f ON f.player_id = p.player_id
)
SELECT platform, COUNT(*) AS installs, SUM(s1) AS completed_tutorial, SUM(s2) AS played_ranked, SUM(s3) AS purchased,
ROUND(100 * SUM(s3) / COUNT(*), 2) AS install_to_purchase_pct
FROM steps
GROUP BY platform
ORDER BY platformAnother way
SELECT p.platform, COUNT(*) AS installs, COUNT(t.player_id) AS completed_tutorial,
COUNT(r.player_id) AS played_ranked, COUNT(b.player_id) AS purchased,
ROUND(100 * COUNT(b.player_id) / COUNT(*), 2) AS install_to_purchase_pct
FROM Player p
LEFT JOIN FunnelEvent t ON t.player_id = p.player_id AND t.event_type = 'tutorial_complete'
LEFT JOIN FunnelEvent r ON r.player_id = t.player_id AND r.event_type = 'first_ranked_match' AND r.occurred_at > t.occurred_at
LEFT JOIN FunnelEvent b ON b.player_id = r.player_id AND b.event_type = 'first_purchase' AND b.occurred_at > r.occurred_at
GROUP BY p.platform
ORDER BY p.platform← Median Completed Match Duration by Game Mode · Total Recruits Down Each Player's Referral Tree →