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.
Problem Overview: You need to identify the top three wineries for each country based on their total points. The dataset may contain multiple records per winery, so scores must first be aggregated before ranking the wineries within each country.
Approach 1: Grouping + Window Function + Left Join (O(n log n) time, O(n) space)
Start by aggregating the total points for every (country, winery) pair using GROUP BY. This step collapses multiple rows into a single score representing the winery's total performance. After aggregation, apply a window function such as ROW_NUMBER() or DENSE_RANK() with PARTITION BY country ORDER BY total_points DESC. This ranks wineries within each country based on their score.
Once each winery has a rank, filter or join the ranked results to extract the top three positions. A common pattern uses separate filtered subqueries for rank 1, 2, and 3 and combines them with LEFT JOIN on the country column. This ensures that countries with fewer than three wineries still appear in the result while leaving missing ranks as NULL. Window functions make the ranking step efficient because they avoid correlated subqueries and repeated scans.
The key insight is separating the problem into two phases: aggregation and ranking. Aggregation computes the score that determines ordering. The window function then assigns a deterministic order inside each country partition. The final join step reshapes the ranked rows into the required output structure.
This approach relies heavily on concepts from SQL, especially window functions and aggregation patterns used in database query design. Sorting inside the window function dominates the cost, which results in roughly O(n log n) time due to partition ordering, while intermediate ranking storage requires O(n) space.
Recommended for interviews: The grouping + window function solution is the expected approach. Interviewers want to see that you first aggregate winery scores correctly and then use ranking functions to extract the top results per group. A naive solution using repeated subqueries demonstrates the idea, but window functions show strong SQL proficiency and scale much better.
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.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subqueries for Top K | O(n^2) | O(1) | Small datasets or when window functions are unavailable |
| Grouping + Window Function Ranking | O(n log n) | O(n) | Standard SQL solution for ranking rows within partitions |
| Grouping + Window Function + Left Join (Final Output) | O(n log n) | O(n) | Best when output requires fixed columns for top 1, 2, and 3 wineries |
Leetcode HARD 2991 - Top Three Wineries MAX IFNULL CONCAT SQL - Explained by Everyday Data Science • Everyday Data Science • 737 views views
Watch 1 more video solutions →Practice Top Three Wineries with our built-in code editor and test cases.
Practice on FleetCode