Identify country with highest sunny-day probability
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write SQL to find the country with the highest probability that a day is sunny. Use the schema and sample data below. Rules: consider a day sunny for a country if any observation for that country on that date has condition = 'Sunny' (case-insensitive); exclude dates where all observations for that country are NULL; compute probability = sunny_days / total_observed_days; only include countries with at least 5 observed days; tie-break by (1) higher probability, (2) higher total_observed_days, then (3) country_name ascending; return one row with columns (country_name, sunny_days, total_days, sunny_day_prob rounded to 3 decimals).
Schema:
- countries(country_id INT PRIMARY KEY, country_name VARCHAR)
- weather_obs(obs_id INT PRIMARY KEY, country_id INT, obs_date DATE, condition VARCHAR, temp_c INT)
Sample data (ASCII):
Table: countries
+------------+--------------+
| country_id | country_name |
+------------+--------------+
| 1 | USA |
| 2 | Canada |
| 3 | Japan |
+------------+--------------+
Table: weather_obs
+--------+------------+------------+-----------+--------+
| obs_id | country_id | obs_date | condition | temp_c |
+--------+------------+------------+-----------+--------+
| 1 | 1 | 2025-06-01 | Sunny | 30 |
| 2 | 1 | 2025-06-02 | Cloudy | 24 |
| 3 | 1 | 2025-06-03 | sunny | 28 |
| 4 | 1 | 2025-06-03 | Rain | 22 |
| 5 | 2 | 2025-06-01 | Rain | 18 |
| 6 | 2 | 2025-06-02 | Sunny | 21 |
| 7 | 2 | 2025-06-02 | Cloudy | 20 |
| 8 | 2 | 2025-06-03 | NULL | 19 |
| 9 | 2 | 2025-06-04 | Sunny | 23 |
| 10 | 3 | 2025-06-01 | Cloudy | 25 |
| 11 | 3 | 2025-06-02 | Sunny | 27 |
| 12 | 3 | 2025-06-03 | Sunny | 29 |
| 13 | 3 | 2025-06-04 | NULL | 26 |
| 14 | 1 | 2025-06-04 | Sunny | 31 |
| 15 | 2 | 2025-06-05 | Sunny | 24 |
| 16 | 3 | 2025-06-05 | Rain | 22 |
+--------+------------+------------+-----------+--------+
Write a single SQL query (standard SQL) that implements the rules above and returns the required row.
Overview: This question evaluates data manipulation competencies including SQL aggregation, NULL handling, case-insensitive condition matching, date-level deduplication, probability calculation, and multi-criteria ordering.
Read the full Capital One Data Scientist interview experience this question came from
Using PostgreSQL, treat a country-date as sunny when any observation on that date has condition = 'Sunny' case-insensitively. Exclude country-dates whose observations all have NULL condition. For each country with at least five observed dates, compute sunny_days / total_observed_days. Return exactly one row for the best country, ordered by higher probability, then higher total observed days, then country_name ascending. Return country_name, sunny_days, total_days, and sunny_day_prob rounded to three decimals.
Tables
countries(country_id INTEGER, country_name TEXT)
weather_obs(obs_id INTEGER, country_id INTEGER, obs_date DATE, condition TEXT, temp_c INTEGER)
Hints
- First collapse multiple weather observations to one row per country and date.
- COUNT(condition) distinguishes an all-NULL day from an observed non-sunny day.
Community answers
Answer by JJYY
with cte as
(select country_id, sum(case when lower(condition) = 'sunny' then 1 else 0 end) as sunny_count from weather_obs
group by country_id ),
cte2 as
(select country_id , count(*) as total_count from weather_obs
group by country_id),
cte3 as
( select cte2.country_id, round(cte.sunny_count/cte2.total_count,2) as prob
from cte2 left join cte on cte2.country_id = cte.country_id ),
cte4 as
(select country_name, prob
from cte3 inner join countries c
on cte3.country_id = c.country_id
order by prob desc)
select country_name, prob
from cte4
limit 1