Build the 2025-26 league table from raw results
The Atlantic Premier League's website lost its standings page, but every result of the 2025-26 season is still in matches (one row per game with home_goals and away_goals). The table must be rebuilt from scratch: a win is 3 points, a draw 1, a loss 0.
Hint for the approach: every match counts twice, once from the home side and once from the away side.
Task: return position, club (name), played, won, drawn, lost, goals_for, goals_against, goal_diff and points. Rank by points (high first), then goal difference (high first), then goals for (high first), then club name A to Z. position is 1, 2, 3, ... in that order with no repeated numbers. Sort the output by position.
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?
UNION ALL the home view and the away view of every match into one list of (club, goals for, goals against), then group.
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.