Top Three Wineries solution and explanation
Google
The 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 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.
Approach overview
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.
Solution
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.
# Write your MySQL query statement below
WITH
T AS (
SELECT
country,
CONCAT(winery, ' (', points, ')') AS winery,
RANK() OVER (
PARTITION BY country
ORDER BY points DESC, winery
) AS rk
FROM (SELECT country, SUM(points) AS points, winery FROM Wineries GROUP BY 1, 3) AS t
)
SELECT
t1.country,
t1.winery AS top_winery,
IFNULL(t2.winery, 'No second winery') AS second_winery,
IFNULL(t3.winery, 'No third winery') AS third_winery
FROM
T AS t1
LEFT JOIN T AS t2 ON t1.country = t2.country AND t1.rk = t2.rk - 1
LEFT JOIN T AS t3 ON t2.country = t3.country AND t2.rk = t3.rk - 1
WHERE t1.rk = 1
ORDER BY 1;Complexity analysis
| 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 |
Frequently asked questions
Is Top Three Wineries easy or hard?
Top Three Wineries is classified as Hard because it combines multiple SQL concepts: aggregation, window functions, ranking within partitions, and reshaping results with joins. Candidates must structure the query carefully to produce the top three wineries for every country.
Top Three Wineries Python/Java solution
This problem is primarily solved using SQL because it focuses on database querying and ranking. In Python or Java environments, the equivalent logic would involve grouping records by country and winery, computing totals, sorting each group, and selecting the top three entries.
How to solve Top Three Wineries in O(n)?
Pure O(n) is generally not achievable because ranking requires ordering the wineries by total points within each country. SQL engines perform partitioned sorting for window functions, which results in O(n log n) complexity. The optimal practical solution uses GROUP BY followed by ROW_NUMBER or DENSE_RANK.
Is Top Three Wineries asked at Google/Amazon/Meta?
SQL ranking and top‑K per group problems frequently appear in interviews at companies like Amazon, Google, and Meta. Variations often ask for the top N items per category using window functions or ranking logic, making this problem good preparation for SQL interview rounds.
What is the best approach for Top Three Wineries ?
The best approach aggregates winery scores with GROUP BY and then ranks them using a window function such as ROW_NUMBER() or DENSE_RANK() partitioned by country. After ranking, filter or join the rows for ranks 1 through 3. This method runs in roughly O(n log n) time due to sorting within partitions and is the standard SQL solution.
What data structure is used in Top Three Wineries ?
The problem relies on relational database operations rather than traditional in-memory data structures. Key SQL features include GROUP BY aggregation, window functions such as ROW_NUMBER or DENSE_RANK, and joins to combine ranked results.
What is the time complexity of Top Three Wineries ?
The typical solution runs in O(n log n) time because the window function must sort rows within each country partition when assigning ranks. Aggregation with GROUP BY runs in O(n). Space complexity is O(n) to store aggregated and ranked intermediate results.