Top Scorer per Squad

An amateur football league hands a golden boot to the top scorer of each squad at the end of the season. If several players in a squad share the highest goal tally, each of them gets a boot.

Return squad_name, player_name and goals for every boot winner, in any order. Squads with no registered players do not appear. A squad whose players all scored 0 still awards boots to all of them.

Tables

squads — One row per squad in the league.

players — One row per registered player.

Example

squads

players

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Window Functions · problem 39 of 70 · SQLite

The idea

The question is "which rows equal their group's maximum", and it must keep ties. Two clean ways do this.

Window function: RANK() OVER (PARTITION BY squad_id ORDER BY goals DESC) numbers players inside each squad from the highest tally down, and tied leaders all get rank 1. Filter rnk = 1 in an outer query (window results cannot be filtered in the same WHERE) and join to squads for the name.

Correlated subquery: keep a player when their goals equal MAX(goals) among players of the same squad. The correlation p2.squad_id = p.squad_id is the important part. Writing goals IN (SELECT MAX(goals) … GROUP BY squad_id) loses it: a player matches any squad's best, not their own.

Both start from players, so squads without players never appear, and a squad where everyone scored 0 returns all its players.

Solution

WITH ranked AS (
  SELECT
    squad_id,
    player_name,
    goals,
    RANK() OVER (PARTITION BY squad_id ORDER BY goals DESC) AS rnk
  FROM players
)
SELECT s.squad_name, r.player_name, r.goals
FROM ranked AS r
JOIN squads AS s ON s.squad_id = r.squad_id
WHERE r.rnk = 1;

Another way

SELECT s.squad_name, p.player_name, p.goals
FROM players AS p
JOIN squads AS s ON s.squad_id = p.squad_id
WHERE p.goals = (
  SELECT MAX(p2.goals)
  FROM players AS p2
  WHERE p2.squad_id = p.squad_id
);

Why common answers fail

GROUP BY with MAX returns one row per squad, so tied top scorers are lost, and the bare player_name is taken from an arbitrary player.

SELECT s.squad_name, p.player_name, MAX(p.goals) AS goals
FROM players AS p
JOIN squads AS s ON s.squad_id = p.squad_id
GROUP BY s.squad_id;

Compares goals with the list of every squad's maximum, so a player whose tally equals another squad's best is wrongly included.

SELECT s.squad_name, p.player_name, p.goals
FROM players AS p
JOIN squads AS s ON s.squad_id = p.squad_id
WHERE p.goals IN (SELECT MAX(goals) FROM players GROUP BY squad_id);

ROW_NUMBER picks exactly one player per squad and drops tied top scorers.

WITH ranked AS (
  SELECT squad_id, player_name, goals,
         ROW_NUMBER() OVER (PARTITION BY squad_id ORDER BY goals DESC) AS rn
  FROM players
)
SELECT s.squad_name, r.player_name, r.goals
FROM ranked AS r JOIN squads AS s ON s.squad_id = r.squad_id
WHERE r.rn = 1;

What the interviewer is checking

This is the classic "row with the group maximum" question; the interviewer is looking for tie handling and a correctly correlated comparison.

Mistakes to avoid

• Selecting a non-aggregated column next to MAX in a GROUP BY (SQLite allows it but returns one arbitrary row).
• Using IN against all groups' maxima without matching the group.
• Using ROW_NUMBER and dropping ties.

Follow-ups to expect

• Also list squads with no players, with NULL for the player (LEFT JOIN from squads).
• Return the second-best tally per squad (DENSE_RANK = 2).
• Award the boot only when the leader is unique (filter squads where COUNT of rank-1 rows is 1).

In real work

Top seller per region, highest-paid role per department and best-performing ad per campaign are all group-maximum queries where ties must be reported.

Practice next