Diagnose SQL with Joins and Group By

You're a Software Engineer on PlayStation's store analytics team, reviewing a query a teammate wrote for a PS Plus regional spend report. It should show one row per player in a region: how much they've spent on RPGs and how many purchases that was.

CREATE TABLE players (
    player_id BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    region VARCHAR(30) NOT NULL
);

CREATE TABLE games (
    game_id INT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    genre VARCHAR(50) NOT NULL
);

CREATE TABLE purchases (
    purchase_id BIGINT PRIMARY KEY,
    player_id BIGINT NOT NULL,
    game_id INT NOT NULL,
    amount_usd NUMERIC(8,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    purchased_at TIMESTAMP NOT NULL
);

SELECT
    p.region,
    p.username,
    g.title,
    SUM(pu.amount_usd) AS total_spent,
    COUNT(pu.purchase_id) AS purchase_count
FROM players p
JOIN purchases pu ON p.player_id = pu.player_id
JOIN games g ON pu.game_id = g.game_id
WHERE g.genre = 'RPG'
  AND p.region = 'EU'
GROUP BY p.region
ORDER BY total_spent DESC;

Product wants one row per EU player, including players who own zero RPGs, with their correct RPG spend. Diagnose everything wrong with this query.

Assume every purchase row is eligible because no status filter is specified.

Output

1. One row per EU player, including players with no RPG purchases.
2. Columns: region, username, total_spent, and purchase_count.
3. Sort by total_spent descending, then username ascending to break ties.