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 Problem OverviewInput: Wine RatingscountrywinerypointsidUSAWineryA951USAWineryA922USAWineryB873FranceWineryC884SUM + RANKBY COUNTRYRanked WineriescountrywineryrankFranceWineryC1USAWineryA1USAWineryB2PIVOTTOP 3Final ResultcountryfirstsecondFranceWineryCNo secondUSAWineryAWineryBKey SQL OperationsGROUP BYcountry, winerySUM(points)ROW_NUMBERPARTITION BY countryORDER BY points DESCCASE WHENrank = 1,2,3Handle missing
Understanding the Visualization
1
Input
Raw wine rating data with multiple entries per winery
2
Aggregate
Sum total points per winery per country
3
Rank
ROW_NUMBER() partitioned by country
4
Pivot
Transform top 3 ranks into separate columns
Key Takeaway
🎯 Key Insight: Use window functions for efficient ranking within groups, then pivot results with conditional logic to handle missing data gracefully.

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