Microsoft: Xbox Hunters With the Most Rare Achievements
The Xbox community team wants to feature its best achievement hunters — the players with the most rare achievements.
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #795
Problem statement
The Xbox community team wants to feature its best achievement hunters — the players with the most rare achievements.
An achievement is rare when it has been unlocked by fewer than 25% of the title's players (TitlePlayer rows for that title). For each gamertag with at least one rare unlock, count rare_unlocks, and rank the gamertags by that count with dense ranking (ties share a rank, no gaps). Return gamertag, rare_unlocks and hunter_rank for the gamertags in the top 3 ranks, ordered by hunter_rank, then gamertag.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: TitlePlayer
| Column | Type |
|---|---|
| gamertag | varchar |
| title_id | int |
Primary key: gamertag, title_id.
One row per player who has played a title.
Table: Achievement
| Column | Type |
|---|---|
| achievement_id | int |
| title_id | int |
| achievement_name | varchar |
Primary key: achievement_id.
One row per achievement a title offers.
Table: AchievementUnlock
| Column | Type |
|---|---|
| gamertag | varchar |
| achievement_id | int |
| unlocked_at | datetime |
Primary key: gamertag, achievement_id.
One row per unlock; only a player of the title can unlock its achievements.
Examples
Example 1
TitlePlayer
| gamertag | title_id |
|---|---|
| NovaRiya | 1 |
| PixelPriya | 1 |
| ShadowKabir | 1 |
| BlazeArjun | 1 |
| EchoZara | 1 |
| ByteMeera | 1 |
| OrbitDev | 1 |
| LunaIra | 1 |
| NovaRiya | 2 |
| PixelPriya | 2 |
| ShadowKabir | 2 |
| BlazeArjun | 2 |
| EchoZara | 2 |
Achievement
| achievement_id | title_id | achievement_name |
|---|---|---|
| 401 | 1 | Legendary Campaign |
| 402 | 1 | Flawless Victory |
| 403 | 1 | Grapple Ace |
| 404 | 1 | Explorer |
| 405 | 2 | Into the Unknown |
| 406 | 2 | Speedrunner |
| 407 | 2 | Completionist |
| 408 | 2 | First Steps |
AchievementUnlock
| gamertag | achievement_id | unlocked_at |
|---|---|---|
| NovaRiya | 401 | 2025-03-01 20:00:00 |
| NovaRiya | 402 | 2025-03-02 21:00:00 |
| PixelPriya | 402 | 2025-03-03 19:00:00 |
| PixelPriya | 403 | 2025-03-04 22:00:00 |
| NovaRiya | 404 | 2025-03-01 18:00:00 |
| PixelPriya | 404 | 2025-03-01 18:30:00 |
| ShadowKabir | 404 | 2025-03-01 19:00:00 |
| BlazeArjun | 404 | 2025-03-02 18:00:00 |
| EchoZara | 404 | 2025-03-02 18:30:00 |
| ByteMeera | 404 | 2025-03-02 19:00:00 |
| NovaRiya | 405 | 2025-04-01 20:00:00 |
| PixelPriya | 406 | 2025-04-02 21:00:00 |
| ShadowKabir | 408 | 2025-04-01 18:00:00 |
| NovaRiya | 408 | 2025-04-01 18:05:00 |
| EchoZara | 408 | 2025-04-01 18:10:00 |
| BlazeArjun | 408 | 2025-04-01 18:15:00 |
| PixelPriya | 408 | 2025-04-01 18:20:00 |
Output
| gamertag | rare_unlocks | hunter_rank |
|---|---|---|
| NovaRiya | 2 | 1 |
| PixelPriya | 2 | 1 |
How to solve Microsoft: Xbox Hunters With the Most Rare Achievements
Build it in layers. Players per title come from TitlePlayer; unlocks per achievement from AchievementUnlock. An achievement is rare when unlocks * 4 < players — integer arithmetic so the 25% boundary is exact: in the 8-player Halo example, an achievement two players unlocked (25%) is not rare, one unlocked by a single player is. An achievement nobody unlocked is rare too, but it gives no one a point.
Next, each gamertag's count of unlocks among the rare achievements, and then DENSE_RANK() OVER (ORDER BY rare_unlocks DESC): tied hunters share a rank and the next count gets the next number, so "top 3 ranks" may return more than three people. RANK() would leave gaps after ties and shrink the list; ROW_NUMBER() would break ties arbitrarily. The rank filter has to live in an outer query because window results are computed after WHERE.
The alternative tests rarity with two correlated counts per unlock and computes the dense rank as 1 + the number of distinct higher counts — the pre-window formulation.
Reference solution (MySQL)
WITH title_players AS (
SELECT title_id, COUNT(*) AS players FROM TitlePlayer GROUP BY title_id
), unlock_counts AS (
SELECT a.achievement_id, a.title_id, COUNT(u.gamertag) AS unlocks
FROM Achievement a
LEFT JOIN AchievementUnlock u ON u.achievement_id = a.achievement_id
GROUP BY a.achievement_id, a.title_id
), rare AS (
SELECT uc.achievement_id
FROM unlock_counts uc JOIN title_players tp ON tp.title_id = uc.title_id
WHERE uc.unlocks * 4 < tp.players
), hunters AS (
SELECT u.gamertag, COUNT(*) AS rare_unlocks
FROM AchievementUnlock u JOIN rare r ON r.achievement_id = u.achievement_id
GROUP BY u.gamertag
), ranked AS (
SELECT gamertag, rare_unlocks, DENSE_RANK() OVER (ORDER BY rare_unlocks DESC) AS hunter_rank
FROM hunters
)
SELECT gamertag, rare_unlocks, hunter_rank
FROM ranked
WHERE hunter_rank <= 3
ORDER BY hunter_rank, gamertagAnother way
WITH hunters AS (
SELECT u.gamertag, COUNT(*) AS rare_unlocks
FROM AchievementUnlock u
JOIN Achievement a ON a.achievement_id = u.achievement_id
WHERE (SELECT COUNT(*) FROM AchievementUnlock x WHERE x.achievement_id = u.achievement_id) * 4
< (SELECT COUNT(*) FROM TitlePlayer tp WHERE tp.title_id = a.title_id)
GROUP BY u.gamertag
), ranked AS (
SELECT h.gamertag, h.rare_unlocks,
1 + (SELECT COUNT(DISTINCT h2.rare_unlocks) FROM hunters h2 WHERE h2.rare_unlocks > h.rare_unlocks) AS hunter_rank
FROM hunters h
)
SELECT gamertag, rare_unlocks, hunter_rank FROM ranked WHERE hunter_rank <= 3 ORDER BY hunter_rank, gamertag← Microsoft: Azure Prepayment Balance and Overage Month by Month · Microsoft: Bing Search Sessions That Ended Without a Click →