I have a database called player.db
These database has two tables.
The tables called person and the other is called match.
person table is
| Player_ID | Player | Country |
|---|---|---|
| 1 | Lionel Messi | Argentina |
| 2 | Luis Suarez | Uruguay |
| 3 | Neymar | Brazil |
match table is
| Match _ID | Game | Player_ID | Date | Season |
|---|---|---|---|---|
| 1 | Uruguay-Paraguay | 2 | 5/3/2019 | 1 |
| 2 | Uruguay-Chile | 2 | 19/3/2019 | 1 |
| 3 | Argentina-Chile | 1 | 22/3/2019 | 1 |
| 4 | Brazil-Guyana | 3 | 3/4/2019 | 1 |
| 5 | Brazil-USA | 3 | 1/6/2020 | 2 |
| 6 | Brazil-Belize | 3 | 3/7/2020 | 2 |
| 7 | Brazil-Suriname | 3 | 5/7/2020 | 2 |
| 8 | Argentina-USA | 1 | 8/8/2020 | 2 |
| 9 | Argentina-Canada | 1 | 3/3/2021 | 3 |
| 10 | Argentina-Grenada | 1 | 8/3/2021 | 3 |
| 11 | Uruguay-Suriname | 2 | 7/4/2021 | 3 |
| 12 | Uruguay-Mexico | 2 | 2/2/2022 | 4 |
| 13 | Uruguay-Jamaica | 2 | 4/2/2022 | 4 |
| 14 | Brazil-Ecuador | 3 | 5/2/2022 | 4 |
My pivot table should look like these:
| Season | Player |
|---|---|
| 1 | Luis Suarez |
| 2 | Neymar |
| 3 | Lionel Messi |
| 4 | Luis Suarez |
I want a sql code which create a pivot table which shows which player played most with topscore in which season year. For example Luis Suarez occured most in season 1.
I started coding in sql, but got not the desired solution
SELECT Player_ID, COUNT(*)FROM match GROUP BY Player_ID HAVING COUNT(*) max
The problem is I got an error and it doesn't create a pivot table which show which player played most in which season.
Join the tables, group by season and player to get the number of matches for each player and use FIRST_VALUE() window function to pick the top player of each season:
SELECT DISTINCT m.Season,
FIRST_VALUE(p.Player) OVER (PARTITION BY m.Season ORDER BY COUNT(*) DESC) Player
FROM match m INNER JOIN person p
ON p.Player_ID = m.Player_ID
GROUP BY m.Season, m.Player_ID;
See the demo.