Major Tournament Title Tally per Player

Medium

A tennis analytics platform maintains player identities and yearly major-event winners. Table: players player_id: unique player identifier player_name: display name of the player Table: championships season_year: season identifier (unique per row) major_clay: winner player_id of the clay major major_grass: winner player_id of the grass major major_hard_us: winner player_id of the late-season hard-court major major_hard_au: winner player_id of the early-season hard-court major Each championships row contains four winner IDs, one for each major event in that season. A player can win zero, one, or multiple majors in the same season. Task: Compute how many major events each player has won across all seasons. Exclude players with zero titles. Output requirements: Return exactly these columns in this order: player_id player_name major_titles_count Sort by major_titles_count in descending order. If multiple players tie on major_titles_count, sort those players by player_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

players:
| player_id | player_name |
|-----------|-------------|
| 1         | Ari         |
| 2         | Blake       |
| 3         | Chen        |

championships:
| season_year | major_clay | major_grass | major_hard_us | major_hard_au |
|-------------|------------|-------------|---------------|---------------|
| 2018        | 1          | 1           | 2             | 2             |
| 2019        | 1          | 2           | 2             | 1             |
| 2020        | 2          | 1           | 2             | 2             |

Output

[
  {"player_id":2,"player_name":"Blake","major_titles_count":7},
  {"player_id":1,"player_name":"Ari","major_titles_count":5}
]

Explanation

Player 2 appears seven times across the four winner columns over three seasons, while player 1 appears five times. Player 3 has zero wins and is excluded.

Example 2

Input

players:
| player_id | player_name |
|-----------|-------------|
| 10        | Inez        |
| 20        | Jiro        |
| 30        | Kira        |
| 40        | Luis        |

championships:
| season_year | major_clay | major_grass | major_hard_us | major_hard_au |
|-------------|------------|-------------|---------------|---------------|
| 2021        | 10         | 20          | 30            | 40            |
| 2022        | 20         | 30          | 40            | 10            |
| 2023        | 30         | 40          | 10            | 20            |
| 2024        | 40         | 10          | 20            | 30            |

Output

[
  {"player_id":10,"player_name":"Inez","major_titles_count":4},
  {"player_id":20,"player_name":"Jiro","major_titles_count":4},
  {"player_id":30,"player_name":"Kira","major_titles_count":4},
  {"player_id":40,"player_name":"Luis","major_titles_count":4}
]

Explanation

Each player wins exactly one major per season over four seasons, producing a complete tie on total titles.

Example 3

Input

players:
| player_id | player_name |
|-----------|-------------|
| 101       | Maya        |
| 102       | Noah        |

championships:
| season_year | major_clay | major_grass | major_hard_us | major_hard_au |
|-------------|------------|-------------|---------------|---------------|
| 2030        | 101        | 101         | 101           | 101           |
| 2031        | 101        | 101         | 101           | 101           |

Output

[
  {"player_id":101,"player_name":"Maya","major_titles_count":8}
]

Explanation

A single dominant player can win multiple majors in a season; each major is counted independently.

Constraints

1 <= number of rows in players <= 50000
0 <= number of rows in championships <= 50000
1 <= player_id <= 1000000000
player_id is unique in players
player_name is a non-empty string
season_year is unique in championships
Each winner column value references an existing players.player_id
A player may win multiple majors in the same season
Exclude players whose total major_titles_count is 0
Return columns exactly as: player_id, player_name, major_titles_count
Sort by major_titles_count DESC, then player_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

players: | player_id | player_name | |-----------|-------------| | 1 | Ari | | 2 | Blake | | 3 | Chen | championships: | season_year | major_clay | major_grass | major_hard_us | major_hard_au | |-------------|------------|-------------|---------------|---------------| | 2018 | 1 | 1 | 2 | 2 | | 2019 | 1 | 2 | 2 | 1 | | 2020 | 2 | 1 | 2 | 2 |

players: | player_id | player_name | |-----------|-------------| | 10 | Inez | | 20 | Jiro | | 30 | Kira | | 40 | Luis | championships: | season_year | major_clay | major_grass | major_hard_us | major_hard_au | |-------------|------------|-------------|---------------|---------------| | 2021 | 10 | 20 | 30 | 40 | | 2022 | 20 | 30 | 40 | 10 | | 2023 | 30 | 40 | 10 | 20 | | 2024 | 40 | 10 | 20 | 30 |

[ {"player_id":10,"player_name":"Inez","major_titles_count":4}, {"player_id":20,"player_name":"Jiro","major_titles_count":4}, {"player_id":30,"player_name":"Kira","major_titles_count":4}, {"player_id":40,"player_name":"Luis","major_titles_count":4} ]

players: | player_id | player_name | |-----------|-------------| | 101 | Maya | | 102 | Noah | championships: | season_year | major_clay | major_grass | major_hard_us | major_hard_au | |-------------|------------|-------------|---------------|---------------| | 2030 | 101 | 101 | 101 | 101 | | 2031 | 101 | 101 | 101 | 101 |