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.
Quick Answer: This question evaluates data manipulation competencies including SQL aggregation, NULL handling, case-insensitive condition matching, date-level deduplication, probability calculation, and multi-criteria ordering.
