Determine Country with Most 'Sunny' Days
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Weather
+------------+------------+---------+
| country | date | weather |
+------------+------------+---------+
| Spain | 2023-07-01 | sunny |
| Spain | 2023-07-02 | cloudy |
| Brazil | 2023-07-01 | sunny |
| Canada | 2023-07-01 | rainy |
| Brazil | 2023-07-02 | sunny |
+------------+------------+---------+
##### Scenario
A travel company keeps daily weather logs for multiple countries and wants to recommend the destination with the highest likelihood of sunny weather.
##### Question
Write an SQL query that returns the country with the greatest number of days classified as 'sunny'. Break ties arbitrarily or return all tied countries.
##### Hints
Aggregate with SUM(CASE WHEN weather = 'sunny' THEN 1 END) and sort.
Overview: This question evaluates proficiency in data aggregation, grouping, and filtering to summarize categorical time-series records using SQL or equivalent Python data-manipulation tools, and it belongs to the Data Manipulation domain with an emphasis on practical application of query and aggregation techniques.
A travel company keeps daily weather logs for multiple countries and wants to recommend the destination with the highest likelihood of sunny weather. Write an SQL query that returns the country (or countries) with the greatest number of days classified as 'sunny'. If multiple countries are tied for the highest number of sunny days, return all of them.
Tables
Weather(country VARCHAR, date DATE, weather VARCHAR)
Hints
- Aggregate counts per country with SUM(CASE WHEN weather = 'sunny' THEN 1 END).
- Compute the maximum sunny_days and filter the aggregated results to only those rows with that maximum, so all ties are returned.