Katkat
Back

Determine Country with Most 'Sunny' Days

Capital OneData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.