The top scorer of every club (ties included)
Each club wants a poster of its leading marksman. In goals every goal records the scoring player (player_id) and the club he scored for (club_id). Careful: four players moved clubs in the January window, so a player's goals must be counted per club he scored for, using goals.club_id and not players.club_id.
When two or more players share the club's highest tally they all get a poster.
Task: return club (name), player (name) and goals (goals scored for that club) for every player whose count equals the best count at that club. Sort by club A to Z, then by player A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| club_id INTEGER | name TEXT | city TEXT | stadium_capacity INTEGER |
|---|---|---|---|
| 1 | Harbor City FC | Harbor City | 48000 |
| 2 | Rivermoor United | Rivermoor | 36000 |
| 3 | Atlas Athletic | Port Atlas | 41000 |
| 4 | Solent Albion | Solent Bay | 29000 |
| 5 | Kestrel Town | Kestrel | 22000 |
| 6 | Ironbridge Wanderers | Ironbridge | 33000 |
Showing the first 6 of 10 rows.
| goal_id INTEGER | match_id INTEGER | player_id INTEGER | club_id INTEGER | minute INTEGER |
|---|---|---|---|---|
| 1 | 1 | 2 | 1 | 53 |
| 2 | 1 | 5 | 1 | 66 |
| 3 | 2 | 62 | 9 | 32 |
| 4 | 2 | 14 | 2 | 77 |
| 5 | 3 | 21 | 3 | 59 |
| 6 | 3 | 21 | 3 | 65 |
Showing the first 6 of 222 rows.
| match_id INTEGER | season TEXT | matchweek INTEGER | match_date TEXT | home_club_id INTEGER | away_club_id INTEGER | home_goals INTEGER | away_goals INTEGER |
|---|---|---|---|---|---|---|---|
| 1 | 2025-26 | 1 | 2025-08-09 | 1 | 10 | 2 | 0 |
| 2 | 2025-26 | 1 | 2025-08-09 | 2 | 9 | 1 | 1 |
| 3 | 2025-26 | 1 | 2025-08-09 | 3 | 8 | 2 | 2 |
| 4 | 2025-26 | 1 | 2025-08-09 | 4 | 7 | 2 | 3 |
| 5 | 2025-26 | 1 | 2025-08-09 | 5 | 6 | 3 | 1 |
| 6 | 2025-26 | 2 | 2025-08-23 | 9 | 1 | 0 | 0 |
Showing the first 6 of 90 rows.
| player_id INTEGER | name TEXT | club_id INTEGER | position TEXT | nationality TEXT | birth_date TEXT |
|---|---|---|---|---|---|
| 1 | Oliver Hughes | 1 | GK | United Kingdom | 1996-02-16 |
| 2 | Mateusz Nowak | 1 | DF | Poland | 2003-12-02 |
| 3 | Caleb Brooks | 1 | DF | United States | 2000-06-16 |
| 4 | Nour Salem | 1 | MF | Egypt | 2006-02-25 |
| 5 | Kofi Owusu | 1 | MF | Ghana | 1994-08-02 |
| 6 | Ethan Hayes | 8 | FW | United States | 1995-10-27 |
Showing the first 6 of 70 rows.
| transfer_id INTEGER | player_id INTEGER | from_club TEXT | to_club_id INTEGER | transfer_date TEXT | fee_musd REAL |
|---|---|---|---|---|---|
| 1 | 2 | Dunmore Athletic | 1 | 2024-10-01 | 2.5 |
| 2 | 5 | Santa Rosa SC | 1 | 2024-11-05 | 8 |
| 3 | 8 | FC Lindenberg | 2 | 2025-07-11 | 18 |
| 4 | 11 | Real Costa Verde | 2 | 2025-04-18 | 0 |
| 5 | 14 | Nordhavn IF | 2 | 2025-02-07 | 4 |
| 6 | 17 | Olympia Kallithea | 3 | 2024-09-10 | 15 |
Showing the first 6 of 26 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
RANK() gives tied players the same number, so keep the rows where the rank is 1.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.