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.
Quick Answer: 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.