Major Tournament Title Tally per Player

MEDIUM20 pts

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:
1. player_id
2. player_name
3. 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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

players =

[{"player_id":1,"player_name":"Ari"},{"player_id":2,"player_name":"Blake"},{"player_id":3,"player_name":"Chen"}]

championships =

[{"season_year":2018,"major_clay":1,"major_grass":1,"major_hard_us":2,"major_hard_au":2},{"season_year":2019,"major_clay":1,"major_grass":2,"major_hard_us":2,"major_hard_au":1},{"season_year":2020,"major_clay":2,"major_grass":1,"major_hard_us":2,"major_hard_au":2}]

Major Tournament Title Tally per Player

Hints

Major Tournament Title Tally per Player

Hints