← Capital One Interview Insights

Capital One·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

Capital One Data Scientist technical screen, one SQL question that looked manageable on the surface but had enough edge cases baked in to trip you up if you moved too fast.

Questions Asked (1)

Q1

Write a single SQL query to find the country with the highest probability of a sunny day. A day counts as sunny if any observation for that country on that date has condition = 'Sunny' (case-insensitive). Exclude dates where every observation for that country is NULL. Probability is sunny_days divided by total observed days, only for countries with at least 5 observed days. Break ties by probability descending, then total days descending, then country name ascending. Return one row with country_name, sunny_days, total_days, and sunny_day_prob rounded to 3 decimal places.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures
Author's notes

The case-insensitive match tripped me up first.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into two stages: first, aggregate observations to the country-date level to determine if each day is sunny and filter out all-NULL days; then compute per-country metrics and apply the ranking logic. Use conditional aggregation and window functions to handle the tie-breaking and limit to one row.

Pro tip: Explicitly state your assumptions about NULL handling and case-insensitivity, and mention that you would validate the query against edge cases like countries with exactly 5 days or ties. This shows attention to detail and data quality.

1. Filter and normalize observations

Exclude rows where condition is NULL, and treat 'Sunny' case-insensitively (e.g., using LOWER(condition) = 'sunny'). This ensures only valid observations are considered.

2. Aggregate to country-date level

Group by country and date, and determine if the day is sunny using MAX(CASE WHEN condition = 'Sunny' THEN 1 ELSE 0 END). This flags a day as sunny if any observation meets the condition.

3. Compute per-country metrics

Group by country, count total observed days (all non-NULL days) and sunny days, then calculate the probability as sunny_days / total_days. Filter to countries with at least 5 total days.

4. Rank and select top country

Order by probability descending, then total_days descending, then country_name ascending, and limit to 1 row. Round the probability to 3 decimal places.

Key Points to Mention

  • Handling NULLs: exclude dates where all observations are NULL, and ignore NULL conditions when determining sunny days.
  • Case-insensitive matching: use LOWER() or ILIKE to treat 'Sunny' regardless of case.
  • Conditional aggregation: use MAX(CASE WHEN ...) to flag sunny days at the country-date level.
  • Filtering threshold: only include countries with at least 5 observed days.
  • Tie-breaking logic: order by probability desc, total_days desc, country_name asc.
  • Rounding: use ROUND(probability, 3) to format the output.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.