Diagnose Slow 50M-Row Query | Dataford Interview Questions

Your question is Diagnose Slow 50M-Row Query. Take a moment with it on the right.

Talk me through your thinking if you like. When you're confident, submit your answer and I'll grade it like a real screen (7/10 or better passes).

Problem

Rockstar Games keeps every in-game and storefront purchase in one very large table. player_purchases has columns purchase_id, player_id, game_title, item_name, amount_usd, purchased_at, platform, and country, and holds roughly 50 million rows. A data analyst wrote this query to look up a game's recent big spenders.

SELECT p.player_id,
       p.game_title,
       p.item_name,
       p.amount_usd,
       (SELECT COUNT(*)
        FROM player_purchases p2
        WHERE p2.player_id = p.player_id) AS total_purchases
FROM player_purchases p
WHERE UPPER(p.game_title) = 'GRAND THEFT AUTO V'
  AND p.purchased_at >= '2026-01-01'
ORDER BY p.amount_usd DESC;

This query is correct but painfully slow against the full 50-million-row table. Explain why it's slow, and what you would change.

Your question is Diagnose Slow 50M-Row Query. Take a moment with it on the right.

Talk me through your thinking if you like. When you're confident, submit your answer and I'll grade it like a real screen (7/10 or better passes).

SELECT p.player_id,
       p.game_title,
       p.item_name,
       p.amount_usd,
       (SELECT COUNT(*)
        FROM player_purchases p2
        WHERE p2.player_id = p.player_id) AS total_purchases
FROM player_purchases p
WHERE UPPER(p.game_title) = 'GRAND THEFT AUTO V'
  AND p.purchased_at >= '2026-01-01'
ORDER BY p.amount_usd DESC;

This query is correct but painfully slow against the full 50-million-row table. Explain why it's slow, and what you would change.