Coding questions · Capital One
Determine Country with Most 'Sunny' Days
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.
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.
Country with Most Sunny Days
Table Weather
| column | type | constraints |
|---|---|---|
| country | VARCHAR | |
| date | DATE | |
| weather | VARCHAR |
Sample data:
| 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 |
Expected output
Return the country or countries with the highest count of 'sunny' days, along with that count.
| country | sunny_days |
|---|---|
| Brazil | 2 |
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.