Country Wine Podium Report

Hard

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 1

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 2

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 3

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.

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

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 |

[ {"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)"} ]

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 |

[ {"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"} ]

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

[ {"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"} ]