Microsoft: Gamerscore Awarded per Xbox Title

Easy
776.

Microsoft: Gamerscore Awarded per Xbox Title

An Xbox achievements analyst is reviewing how much Gamerscore each game has handed out to players.

For every title in the catalogue, return title_name, unlocks (the number of achievement unlocks for the title) and gamerscore_awarded (the sum of the Gamerscore of those unlocks). A title with no achievements, or none unlocked yet, appears with 0 in both columns. Order by gamerscore_awarded descending, then title_name ascending.

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: XboxTitle

ColumnType
title_idint
title_namevarchar

Primary key: title_id.

One row per game; title_name is unique.

Table: Achievement

ColumnType
achievement_idint
title_idint
gamerscoreint

Primary key: achievement_id.

One row per achievement a title offers, with the Gamerscore it is worth.

Table: AchievementUnlock

ColumnType
gamertagvarchar
achievement_idint
unlocked_atdatetime

Primary key (together): gamertag, achievement_id.

One row per player per achievement they unlocked.

Example

The rows must come back in the order shown.

XboxTitle

title_idtitle_name
1Forza Horizon 5
2Halo Infinite
3Starfield
4Sea of Thieves

Achievement

achievement_idtitle_idgamerscore
101110
102150
103220
1042100
105315

AchievementUnlock

gamertagachievement_idunlocked_at
ShadowKabir1012025-02-01 18:00:00
ShadowKabir1022025-02-03 21:30:00
PixelPriya1012025-02-04 20:00:00
PixelPriya1032025-02-05 22:15:00
NovaRiya1042025-02-06 19:45:00

Output

title_nameunlocksgamerscore_awarded
Halo Infinite2120
Forza Horizon 5370
Sea of Thieves00
Starfield00
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: Gamerscore Awarded per Xbox Title — SQL Problem

An Xbox achievements analyst is reviewing how much Gamerscore each game has handed out to players.

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #776

Problem statement

An Xbox achievements analyst is reviewing how much Gamerscore each game has handed out to players.

For every title in the catalogue, return title_name, unlocks (the number of achievement unlocks for the title) and gamerscore_awarded (the sum of the Gamerscore of those unlocks). A title with no achievements, or none unlocked yet, appears with 0 in both columns. Order by gamerscore_awarded descending, then title_name ascending.

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: XboxTitle

ColumnType
title_idint
title_namevarchar

Primary key: title_id.

One row per game; title_name is unique.

Table: Achievement

ColumnType
achievement_idint
title_idint
gamerscoreint

Primary key: achievement_id.

One row per achievement a title offers, with the Gamerscore it is worth.

Table: AchievementUnlock

ColumnType
gamertagvarchar
achievement_idint
unlocked_atdatetime

Primary key: gamertag, achievement_id.

One row per player per achievement they unlocked.

Examples

Example 1

XboxTitle

title_idtitle_name
1Forza Horizon 5
2Halo Infinite
3Starfield
4Sea of Thieves

Achievement

achievement_idtitle_idgamerscore
101110
102150
103220
1042100
105315

AchievementUnlock

gamertagachievement_idunlocked_at
ShadowKabir1012025-02-01 18:00:00
ShadowKabir1022025-02-03 21:30:00
PixelPriya1012025-02-04 20:00:00
PixelPriya1032025-02-05 22:15:00
NovaRiya1042025-02-06 19:45:00

Output

title_nameunlocksgamerscore_awarded
Halo Infinite2120
Forza Horizon 5370
Sea of Thieves00
Starfield00

How to solve Microsoft: Gamerscore Awarded per Xbox Title

Each title must appear even when it has nothing to report, so the query starts from XboxTitle and LEFT JOINs down the chain: title → achievements → unlocks. A title with no achievements keeps one row of NULLs; an achievement no one unlocked keeps one row with the unlock columns NULL.

That second case is why the sum is conditional. The row of an unlocked-by-nobody achievement still carries its gamerscore, so a plain SUM(a.gamerscore) would award points nobody earned. Summing only where u.gamertag IS NOT NULL fixes it, and COALESCE(…, 0) turns the empty sum (NULL) into 0. COUNT(u.gamertag) likewise counts only real unlocks.

The alternative uses two correlated subqueries per title — easier to read, but it scans the unlocks twice per title; the join version does one pass with a GROUP BY.

Reference solution (MySQL)

SELECT t.title_name,
       COUNT(u.gamertag) AS unlocks,
       COALESCE(SUM(CASE WHEN u.gamertag IS NOT NULL THEN a.gamerscore END), 0) AS gamerscore_awarded
FROM XboxTitle t
LEFT JOIN Achievement a ON a.title_id = t.title_id
LEFT JOIN AchievementUnlock u ON u.achievement_id = a.achievement_id
GROUP BY t.title_id, t.title_name
ORDER BY gamerscore_awarded DESC, t.title_name

Another way

SELECT t.title_name,
  (SELECT COUNT(*) FROM AchievementUnlock u JOIN Achievement a ON a.achievement_id = u.achievement_id WHERE a.title_id = t.title_id) AS unlocks,
  (SELECT COALESCE(SUM(a.gamerscore), 0) FROM AchievementUnlock u JOIN Achievement a ON a.achievement_id = u.achievement_id WHERE a.title_id = t.title_id) AS gamerscore_awarded
FROM XboxTitle t
ORDER BY gamerscore_awarded DESC, title_name

← Microsoft: Licensed Users With No Successful Sign-In · Microsoft: Outlook Mailboxes at 90% of Their Quota or More →