New-Player Funnel From Install to First Purchase by Platform

Hard
196.

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

ColumnTypeValues
player_idint
platformenumandroid, ios, pc
installed_atdatetime

Primary key: player_id.

One row per install.

Table: FunnelEvent

ColumnTypeValues
player_idint
event_typeenumtutorial_complete, first_ranked_match, first_purchase
occurred_atdatetime

Primary key (together): player_id, event_type.

Each milestone happens at most once per player, always after the install.

Example

The rows must come back in the order shown.

Player

player_idplatforminstalled_at
1android2025-05-01 10:00:00
2android2025-05-01 11:00:00
3android2025-05-02 09:00:00
4ios2025-05-02 12:00:00
5ios2025-05-03 08:00:00
6pc2025-05-03 20:00:00

FunnelEvent

player_idevent_typeoccurred_at
1tutorial_complete2025-05-01 10:20:00
1first_ranked_match2025-05-02 19:00:00
1first_purchase2025-05-04 21:00:00
2tutorial_complete2025-05-01 11:30:00
2first_purchase2025-05-01 12:00:00
3first_ranked_match2025-05-02 10:00:00
3tutorial_complete2025-05-02 11:00:00
4tutorial_complete2025-05-02 12:15:00
4first_ranked_match2025-05-03 18:00:00
4first_purchase2025-05-03 18:00:00

Output

platforminstallscompleted_tutorialplayed_rankedpurchasedinstall_to_purchase_pct
android331133.33
ios21100
pc10000
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.

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

ColumnType
player_idint
platformenum(android, ios, pc)
installed_atdatetime

Primary key: player_id.

One row per install.

Table: FunnelEvent

ColumnType
player_idint
event_typeenum(tutorial_complete, first_ranked_match, first_purchase)
occurred_atdatetime

Primary key: player_id, event_type.

Each milestone happens at most once per player, always after the install.

Examples

Example 1

Player

player_idplatforminstalled_at
1android2025-05-01 10:00:00
2android2025-05-01 11:00:00
3android2025-05-02 09:00:00
4ios2025-05-02 12:00:00
5ios2025-05-03 08:00:00
6pc2025-05-03 20:00:00

FunnelEvent

player_idevent_typeoccurred_at
1tutorial_complete2025-05-01 10:20:00
1first_ranked_match2025-05-02 19:00:00
1first_purchase2025-05-04 21:00:00
2tutorial_complete2025-05-01 11:30:00
2first_purchase2025-05-01 12:00:00
3first_ranked_match2025-05-02 10:00:00
3tutorial_complete2025-05-02 11:00:00
4tutorial_complete2025-05-02 12:15:00
4first_ranked_match2025-05-03 18:00:00
4first_purchase2025-05-03 18:00:00

Output

platforminstallscompleted_tutorialplayed_rankedpurchasedinstall_to_purchase_pct
android331133.33
ios21100
pc10000

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 platform

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