Top Three Wineries - Problem

Given a Wineries table containing wine ratings data, find the top three wineries in each country based on their total points.

Key Requirements:

  • Group wineries by country and calculate total points per winery
  • Rank wineries within each country by total points (descending)
  • If wineries have the same total points, order by winery name (ascending)
  • Show exactly 3 ranks per country with special handling for missing ranks
  • If no second winery exists, output 'No second winery'
  • If no third winery exists, output 'No third winery'
  • Order final results by country name (ascending)

This problem combines aggregation, window functions, and conditional logic to handle ranking scenarios with missing data.

Table Schema

Wineries
Column Name Type Description
id PK int Unique identifier for each wine entry
country varchar Country where the winery is located
points int Points awarded to this wine
winery varchar Name of the winery
Primary Key: id
Note: Each row represents a single wine rating. Multiple rows can exist for the same winery.

Input & Output

Example 1 — Multiple Countries with Different Winery Counts
Input Table:
id country points winery
1 USA 95 Winery A
2 USA 87 Winery B
3 USA 92 Winery A
4 France 88 Winery C
5 Italy 90 Winery D
6 Italy 85 Winery E
7 Italy 83 Winery F
Output:
country first_winery second_winery third_winery
France Winery C No second winery No third winery
Italy Winery D Winery E Winery F
USA Winery A Winery B No third winery
💡 Note:

USA: Winery A has total 187 points (95+92), Winery B has 87 points. Italy has exactly 3 wineries. France has only 1 winery, so second and third positions show the required messages.
Example 2 — Tie-breaking by Winery Name
Input Table:
id country points winery
1 Spain 90 Winery Z
2 Spain 90 Winery A
3 Spain 85 Winery M
Output:
country first_winery second_winery third_winery
Spain Winery A Winery Z Winery M
💡 Note:

Winery A and Winery Z both have 90 points, but Winery A comes first alphabetically. Winery M has fewer points so ranks third.

Constraints

  • 1 ≤ id ≤ 10000
  • country and winery consist of alphanumeric characters and spaces
  • 1 ≤ points ≤ 100
  • Each country will have at least one winery

Visualization

Tap to expand
Top Three Wineries - Solution Infographic INPUT winery country points WineryA France 95 WineryB France 90 WineryA France 88 WineryC Italy 92 WineryD Italy 85 ... Data Structure wine_reviews table (winery, country, points) Multiple rows per winery Goal: Top 3 per country Handle ties + missing data by total points ALGORITHM STEPS 1 Group by Country GROUP BY country, winery 2 Sum Points SUM(points) per winery 3 Rank Wineries ROW_NUMBER() or RANK() 4 Handle Missing COALESCE for nulls Ranking Logic 1st 2nd 3rd ORDER BY points DESC, name ASC FINAL RESULT country top_1 top_2 top_3 France WineryA (183) WineryB (90) WineryE (75) Italy WineryC (92) WineryD (85) No third winery Output Format top_winery: "WineryA (183)" second_winery: "WineryB (90)" Missing: "No second winery" OK - Query Complete Top 3 wineries per country Tie Handling Same points --> sort by name ASC Key Insight: Use DENSE_RANK() or ROW_NUMBER() with PARTITION BY country to rank wineries within each country. COALESCE handles missing 2nd/3rd wineries with custom messages. Aggregate points first, then rank. Window functions + conditional aggregation = efficient single-pass solution with O(n log n) complexity. TutorialsPoint - Top Three Wineries | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
23.5K Views
Medium Frequency
~20 min Avg. Time
876 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen