2991. Top Three Wineries π
Description
Table: Wineries
+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | country | varchar | | points | int | | winery | varchar | +-------------+----------+ id is column of unique values for this table. This table contains id, country, points, and winery.
Write a solution to find the top three wineries in each country based on their total points. If multiple wineries have the same total points, order them by winery
name in ascending order. If there's no second winery, output 'No second winery,' and if there's no third winery, output 'No third winery.'
Return the result table ordered by country
in ascending order.
The result format is in the following example.
Example 1:
Input: Wineries table: +-----+-----------+--------+-----------------+ | id | country | points | winery | +-----+-----------+--------+-----------------+ | 103 | Australia | 84 | WhisperingPines | | 737 | Australia | 85 | GrapesGalore | | 848 | Australia | 100 | HarmonyHill | | 222 | Hungary | 60 | MoonlitCellars | | 116 | USA | 47 | RoyalVines | | 124 | USA | 45 | Eagle'sNest | | 648 | India | 69 | SunsetVines | | 894 | USA | 39 | RoyalVines | | 677 | USA | 9 | PacificCrest | +-----+-----------+--------+-----------------+ Output: +-----------+---------------------+-------------------+----------------------+ | country | top_winery | second_winery | third_winery | +-----------+---------------------+-------------------+----------------------+ | Australia | HarmonyHill (100) | GrapesGalore (85) | WhisperingPines (84) | | Hungary | MoonlitCellars (60) | No second winery | No third winery | | India | SunsetVines (69) | No second winery | No third winery | | USA | RoyalVines (86) | Eagle'sNest (45) | PacificCrest (9) | +-----------+---------------------+-------------------+----------------------+ Explanation For Australia - HarmonyHill Winery accumulates the highest score of 100 points in Australia. - GrapesGalore Winery has a total of 85 points, securing the second-highest position in Australia. - WhisperingPines Winery has a total of 80 points, ranking as the third-highest. For Hungary - MoonlitCellars is the sole winery, accruing 60 points, automatically making it the highest. There is no second or third winery. For India - SunsetVines is the sole winery, earning 69 points, making it the top winery. There is no second or third winery. For the USA - RoyalVines Wines accumulates a total of 47 + 39 = 86 points, claiming the highest position in the USA. - Eagle'sNest has a total of 45 points, securing the second-highest position in the USA. - PacificCrest accumulates 9 points, ranking as the third-highest winery in the USA Output table is ordered by country in ascending order.
Solutions
Solution 1: Grouping + Window Function + Left Join
We can first group the Wineries
table by country
and winery
, calculate the total score points
for each group, then use the window function RANK()
to group the data by country
again, sort by points
in descending order and winery
in ascending order, and use the CONCAT()
function to concatenate winery
and points
, resulting in the following data, denoted as table T
:
country | winery | rk |
---|---|---|
Australia | HarmonyHill (100) | 1 |
Australia | GrapesGalore (85) | 2 |
Australia | WhisperingPines (84) | 3 |
Hungary | MoonlitCellars (60) | 1 |
India | SunsetVines (69) | 1 |
USA | RoyalVines (86) | 1 |
USA | Eagle'sNest (45) | 2 |
USA | PacificCrest (9) | 3 |
Next, we just need to filter out the data where rk = 1
, then join table T
to itself twice, connecting the data where rk = 2
and rk = 3
respectively, to get the final result.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
|