# Exercise 12：1194.Tournament Winners

## 1.Description

Table: `Players`

```
+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id is the primary key of this table.
Each row of this table indicates the group of each player
```

Table: `Matches`

```
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
- match_id is the primary key of this table.
- Each row is a record of a match, first_player and second_player 
contain the player_id of each match.
- first_score and second_score contain the number of points of the first_player 
and second_player respectively.
- You may assume that, in each match, players belongs to the same group.

```

The winner in each group is the player who scored the maximum total points within the group. In the case of a tie, the **lowest** player\_id wins.

Write an SQL query to find the winner in each group.

The query result format is in the following example:

```
Players table:
+-----------+------------+
| player_id | group_id   |
+-----------+------------+
| 15        | 1          |
| 25        | 1          |
| 30        | 1          |
| 45        | 1          |
| 10        | 2          |
| 35        | 2          |
| 50        | 2          |
| 20        | 3          |
| 40        | 3          |
+-----------+------------+

Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id   | first_player | second_player | first_score | second_score |
+------------+--------------+---------------+-------------+--------------+
| 1          | 15           | 45            | 3           | 0            |
| 2          | 30           | 25            | 1           | 2            |
| 3          | 30           | 15            | 2           | 0            |
| 4          | 40           | 20            | 5           | 2            |
| 5          | 35           | 50            | 1           | 1            |
+------------+--------------+---------------+-------------+--------------+

Result table:
+-----------+------------+
| group_id  | player_id  |
+-----------+------------+ 
| 1         | 15         |
| 2         | 35         |
| 3         | 40         |
+-----------+------------+
```

## 2.Create Table and insert into values

```sql
create table if not exists leetcode.ex_1194_players
(player_id	string ,group_id	string) stored as orc ;

INSERT INTO table leetcode.ex_1194_players VALUES
('15', '1'),          
('25', '1'),          
('30', '1'),          
('45', '1'),          
('10', '2'),          
('35', '2'),          
('50', '2'),          
('20', '3'),          
('40', '3')
;

create table if not exists leetcode.ex_1194_matches
(match_id				string, 
 first_player 	string,
 second_player 	string,
 first_score		int,
 second_score		int
) stored as orc ;

INSERT INTO table leetcode.ex_1194_matches VALUES
('1', '15', '45', '3', '0'),  
('2', '30', '25', '1', '2'),  
('3', '30', '15', '2', '0'),  
('4', '40', '20', '5', '2'), 
('5', '35', '50', '1', '1')        
```

{% hint style="info" %}
This Problem test how to use UNION and Window Function, pay attention to the type of UNION.
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://chiu-kuohsin.gitbook.io/leetcode-database-solution-with-hive-sql/exercise-12-1194.tournament-winners.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.