Country Wine Podium Report

HARD30 pts

A beverage intelligence platform tracks tasting points from winery events around the world.

You are given one table:

Wineries columns:

id: unique tasting event record ID
country: country name
points: integer points contributed by this tasting record
winery: winery label

A winery can appear in multiple rows within the same country. Its final score for that country is the sum of all points across its rows.

Task: For each country, produce a three-position podium of wineries:

Aggregate total points per (country, winery).
Rank wineries inside each country by:
- total points descending
- winery name ascending (when totals tie)
Return rank #1, rank #2, and rank #3 as formatted strings: <winery_name> (<total_points>)

Missing position rules:

If a country has no rank #2, output "No second winery"
If a country has no rank #3, output "No third winery"

Output columns (exact order):

country
top_winery
second_winery
third_winery

Sort final output by country in ascending order.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Wineries:
| 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     | EaglesNest      |
| 648 | India     | 69     | SunsetVines     |
| 894 | USA       | 39     | RoyalVines      |
| 677 | USA       | 9      | PacificCrest    |

Output

[
  {"country":"Australia","top_winery":"HarmonyHill (100)","second_winery":"GrapesGalore (85)","third_winery":"WhisperingPines (84)"},
  {"country":"Hungary","top_winery":"MoonlitCellars (60)","second_winery":"No second winery","third_winery":"No third winery"},
  {"country":"India","top_winery":"SunsetVines (69)","second_winery":"No second winery","third_winery":"No third winery"},
  {"country":"USA","top_winery":"RoyalVines (86)","second_winery":"EaglesNest (45)","third_winery":"PacificCrest (9)"}
]

Explanation

RoyalVines is aggregated to 86 points in USA (47 + 39). In countries with only one winery, second and third slots use default strings.

Example

Input

Wineries:
| id | country | points | winery    |
|----|---------|--------|-----------|
| 1  | France  | 30     | AmberOak  |
| 2  | France  | 20     | AmberOak  |
| 3  | France  | 50     | BlueCellar|
| 4  | France  | 40     | CedarPeak |
| 5  | France  | 5      | DeltaVale |
| 6  | France  | 35     | DeltaVale |
| 7  | Italy   | 55     | LunaEstate|
| 8  | Italy   | 10     | MareVista |

Output

[
  {"country":"France","top_winery":"AmberOak (50)","second_winery":"BlueCellar (50)","third_winery":"CedarPeak (40)"},
  {"country":"Italy","top_winery":"LunaEstate (55)","second_winery":"MareVista (10)","third_winery":"No third winery"}
]

Explanation

In France, AmberOak and BlueCellar tie at 50, so alphabetical order places AmberOak first.

Example

Input

Wineries:
| id | country | points | winery       |
|----|---------|--------|--------------|
| 50 | Chile   | 88     | AndesBloom   |
| 51 | Peru    | 45     | CuscoCellars |
| 52 | Peru    | 61     | PiscoHeights |

Output

[
  {"country":"Chile","top_winery":"AndesBloom (88)","second_winery":"No second winery","third_winery":"No third winery"},
  {"country":"Peru","top_winery":"PiscoHeights (61)","second_winery":"CuscoCellars (45)","third_winery":"No third winery"}
]

Explanation

Each country is evaluated independently. Chile has one winery, while Peru has two.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Wineries <= 100000
1 <= id <= 1000000000
id values are unique within each test case
1 <= length of country <= 100
1 <= length of winery <= 100
0 <= points <= 100
A winery may appear in multiple rows within the same country
Ranking must use total points DESC, then winery ASC
Output columns must be exactly: country, top_winery, second_winery, third_winery
Sort result by country ASC
SQL submissions are evaluated with SQLite 3.27.2 semantics
Pandas submissions must return a DataFrame with the exact output schema

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Wineries =

[{"id":103,"country":"Australia","points":84,"winery":"WhisperingPines"},{"id":737,"country":"Australia","points":85,"winery":"GrapesGalore"},{"id":848,"country":"Australia","points":100,"winery":"HarmonyHill"},{"id":222,"country":"Hungary","points":60,"winery":"MoonlitCellars"},{"id":116,"country":"USA","points":47,"winery":"RoyalVines"},{"id":124,"country":"USA","points":45,"winery":"EaglesNest"},{"id":648,"country":"India","points":69,"winery":"SunsetVines"},{"id":894,"country":"USA","points":39,"winery":"RoyalVines"},{"id":677,"country":"USA","points":9,"winery":"PacificCrest"}]

Country Wine Podium Report

Hints

Country Wine Podium Report

Hints