Top Three Wineries

Problem

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 bycountry inascending order .

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38


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.

Solution

Method 1 – Window Functions and Aggregation

Intuition

Aggregate total points per winery per country, then use window functions to rank them and pivot the top three into columns, filling missing with the required string.

Approach

Aggregate total points for each (country, winery).
Rank wineries within each country by total points (desc), then winery name (asc).
Pivot the top three into columns, using COALESCE to fill missing with the required string.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


WITH ranked AS (
  SELECT country, winery, SUM(points) AS total_points,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(points) DESC, winery ASC) AS rnk
    FROM Wineries
   GROUP BY country, winery
)
SELECT country,
       CONCAT(COALESCE(MAX(CASE WHEN rnk = 1 THEN winery END), 'No top winery'), ' (', COALESCE(MAX(CASE WHEN rnk = 1 THEN total_points END), 'No top winery'), ')') AS top_winery,
       COALESCE(CONCAT(MAX(CASE WHEN rnk = 2 THEN winery END), ' (', MAX(CASE WHEN rnk = 2 THEN total_points END), ')'), 'No second winery') AS second_winery,
       COALESCE(CONCAT(MAX(CASE WHEN rnk = 3 THEN winery END), ' (', MAX(CASE WHEN rnk = 3 THEN total_points END), ')'), 'No third winery') AS third_winery
  FROM ranked
 GROUP BY country
 ORDER BY country;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


WITH ranked AS (
  SELECT country, winery, SUM(points) AS total_points,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(points) DESC, winery ASC) AS rnk
    FROM Wineries
   GROUP BY country, winery
)
SELECT country,
       COALESCE(MAX(CASE WHEN rnk = 1 THEN winery || ' (' || total_points || ')' END), 'No top winery') AS top_winery,
       COALESCE(MAX(CASE WHEN rnk = 2 THEN winery || ' (' || total_points || ')' END), 'No second winery') AS second_winery,
       COALESCE(MAX(CASE WHEN rnk = 3 THEN winery || ' (' || total_points || ')' END), 'No third winery') AS third_winery
  FROM ranked
 GROUP BY country
 ORDER BY country;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


WITH ranked AS (
  SELECT country, winery, SUM(points) AS total_points,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(points) DESC, winery ASC) AS rnk
    FROM Wineries
   GROUP BY country, winery
)
SELECT country,
       COALESCE(MAX(CASE WHEN rnk = 1 THEN winery || ' (' || total_points || ')' END), 'No top winery') AS top_winery,
       COALESCE(MAX(CASE WHEN rnk = 2 THEN winery || ' (' || total_points || ')' END), 'No second winery') AS second_winery,
       COALESCE(MAX(CASE WHEN rnk = 3 THEN winery || ' (' || total_points || ')' END), 'No third winery') AS third_winery
  FROM ranked
 GROUP BY country
 ORDER BY country;

Complexity

⏰ Time complexity: O(n log n)
🧺 Space complexity: O(n)

Problem#

Examples#

Solution#

Method 1 – Window Functions and Aggregation#

Intuition#

Approach#

Code#

Complexity#