Microsoft: Xbox Hunters With the Most Rare Achievements

Hard
795.

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.

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

ColumnType
gamertagvarchar
title_idint

Primary key (together): gamertag, title_id.

One row per player who has played a title.

Table: Achievement

ColumnType
achievement_idint
title_idint
achievement_namevarchar

Primary key: achievement_id.

One row per achievement a title offers.

Table: AchievementUnlock

ColumnType
gamertagvarchar
achievement_idint
unlocked_atdatetime

Primary key (together): gamertag, achievement_id.

One row per unlock; only a player of the title can unlock its achievements.

Example

The rows must come back in the order shown.

TitlePlayer

gamertagtitle_id
NovaRiya1
PixelPriya1
ShadowKabir1
BlazeArjun1
EchoZara1
ByteMeera1
OrbitDev1
LunaIra1
NovaRiya2
PixelPriya2
3 more rows

Achievement

achievement_idtitle_idachievement_name
4011Legendary Campaign
4021Flawless Victory
4031Grapple Ace
4041Explorer
4052Into the Unknown
4062Speedrunner
4072Completionist
4082First Steps

AchievementUnlock

gamertagachievement_idunlocked_at
NovaRiya4012025-03-01 20:00:00
NovaRiya4022025-03-02 21:00:00
PixelPriya4022025-03-03 19:00:00
PixelPriya4032025-03-04 22:00:00
NovaRiya4042025-03-01 18:00:00
PixelPriya4042025-03-01 18:30:00
ShadowKabir4042025-03-01 19:00:00
BlazeArjun4042025-03-02 18:00:00
EchoZara4042025-03-02 18:30:00
ByteMeera4042025-03-02 19:00:00
7 more rows

Output

gamertagrare_unlockshunter_rank
NovaRiya21
PixelPriya21
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.

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

ColumnType
gamertagvarchar
title_idint

Primary key: gamertag, title_id.

One row per player who has played a title.

Table: Achievement

ColumnType
achievement_idint
title_idint
achievement_namevarchar

Primary key: achievement_id.

One row per achievement a title offers.

Table: AchievementUnlock

ColumnType
gamertagvarchar
achievement_idint
unlocked_atdatetime

Primary key: gamertag, achievement_id.

One row per unlock; only a player of the title can unlock its achievements.

Examples

Example 1

TitlePlayer

gamertagtitle_id
NovaRiya1
PixelPriya1
ShadowKabir1
BlazeArjun1
EchoZara1
ByteMeera1
OrbitDev1
LunaIra1
NovaRiya2
PixelPriya2
ShadowKabir2
BlazeArjun2
EchoZara2

Achievement

achievement_idtitle_idachievement_name
4011Legendary Campaign
4021Flawless Victory
4031Grapple Ace
4041Explorer
4052Into the Unknown
4062Speedrunner
4072Completionist
4082First Steps

AchievementUnlock

gamertagachievement_idunlocked_at
NovaRiya4012025-03-01 20:00:00
NovaRiya4022025-03-02 21:00:00
PixelPriya4022025-03-03 19:00:00
PixelPriya4032025-03-04 22:00:00
NovaRiya4042025-03-01 18:00:00
PixelPriya4042025-03-01 18:30:00
ShadowKabir4042025-03-01 19:00:00
BlazeArjun4042025-03-02 18:00:00
EchoZara4042025-03-02 18:30:00
ByteMeera4042025-03-02 19:00:00
NovaRiya4052025-04-01 20:00:00
PixelPriya4062025-04-02 21:00:00
ShadowKabir4082025-04-01 18:00:00
NovaRiya4082025-04-01 18:05:00
EchoZara4082025-04-01 18:10:00
BlazeArjun4082025-04-01 18:15:00
PixelPriya4082025-04-01 18:20:00

Output

gamertagrare_unlockshunter_rank
NovaRiya21
PixelPriya21

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

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