Coding questions · Capital One
Identify country with highest sunny-day probability
This question evaluates data manipulation competencies including SQL aggregation, NULL handling, case-insensitive condition matching, date-level deduplication, probability calculation, and multi-criteria ordering.
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.
Identify the Country with the Highest Sunny-Day Probability
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.
Table countries
| column | type | constraints |
|---|---|---|
| country_id | INTEGER | PRIMARY KEY |
| country_name | TEXT | NOT NULL |
Sample data:
| country_id | country_name |
|---|---|
| 1 | USA |
| 2 | Canada |
| 3 | Japan |
| 4 | Norway |
| 5 | France |
Table weather_obs
| column | type | constraints |
|---|---|---|
| obs_id | INTEGER | PRIMARY KEY |
| country_id | INTEGER | NOT NULL |
| obs_date | DATE | NOT NULL |
| condition | TEXT | |
| temp_c | INTEGER |
Sample data:
| condition | country_id | obs_date | obs_id | temp_c |
|---|---|---|---|---|
| Sunny | 1 | 2025-06-01 | 1 | 30 |
| Cloudy | 1 | 2025-06-02 | 2 | 24 |
| sunny | 1 | 2025-06-03 | 3 | 28 |
| Rain | 1 | 2025-06-03 | 4 | 22 |
| Sunny | 1 | 2025-06-04 | 5 | 31 |
| Rain | 1 | 2025-06-05 | 6 | 21 |
| NULL | 1 | 2025-06-06 | 7 | 20 |
| Rain | 2 | 2025-06-01 | 8 | 18 |
| Sunny | 2 | 2025-06-02 | 9 | 21 |
| Cloudy | 2 | 2025-06-02 | 10 | 20 |
| NULL | 2 | 2025-06-03 | 11 | 19 |
| Sunny | 2 | 2025-06-04 | 12 | 23 |
| Sunny | 2 | 2025-06-05 | 13 | 24 |
| Rain | 2 | 2025-06-06 | 14 | 17 |
| Cloudy | 2 | 2025-06-07 | 15 | 19 |
| Cloudy | 3 | 2025-06-01 | 16 | 25 |
| Sunny | 3 | 2025-06-02 | 17 | 27 |
| Sunny | 3 | 2025-06-03 | 18 | 29 |
| NULL | 3 | 2025-06-04 | 19 | 26 |
| Rain | 3 | 2025-06-05 | 20 | 22 |
| Sunny | 3 | 2025-06-06 | 21 | 28 |
| Sunny | 3 | 2025-06-07 | 22 | 30 |
| Sunny | 4 | 2025-06-01 | 23 | 14 |
| Sunny | 4 | 2025-06-02 | 24 | 15 |
| Sunny | 4 | 2025-06-03 | 25 | 16 |
| Sunny | 4 | 2025-06-04 | 26 | 17 |
| Sunny | 5 | 2025-06-01 | 27 | 23 |
| SUNNY | 5 | 2025-06-02 | 28 | 24 |
| Rain | 5 | 2025-06-03 | 29 | 20 |
| Sunny | 5 | 2025-06-04 | 30 | 25 |
| Cloudy | 5 | 2025-06-05 | 31 | 22 |
| Sunny | 5 | 2025-06-06 | 32 | 26 |
Expected output
| country_name | sunny_days | total_days | sunny_day_prob |
|---|---|---|---|
| France | 4 | 6 | 0.667 |
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.
- Apply the minimum-day rule before the final deterministic LIMIT 1.