Capital One·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

Capital One data scientist interview, SQL-heavy technical screen. One big multi-part query question that covered joins, window logic, conditional aggregation, and nulls all at once. Felt like a take-home but it was live.

Questions Asked (1)

Write a single SQL query or CTE pipeline across a students/classes/exams/scores schema that: filters to exams in calendar year 2024, creates an adjusted score (NULL if below 60, else the raw score), fills NULLs with zero for display and averaging, and aggregates per class to get pass count, fail count, honors student count, average adjusted score, and distinct exam count. Students with no scores must still appear via LEFT JOIN, and results should be ordered by class name.

Data ModelingTechnical Trade-offsProduct Analytics & Metrics

Author's notes

This one had a lot of moving parts and I kept second-guessing the LEFT JOIN placement.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and business definitions (e.g., what constitutes an honors student, how to handle exams with no scores). Then outline a CTE pipeline that filters exams to 2024, computes adjusted scores with NULL for below 60, fills NULLs with zero for aggregation, and finally LEFT JOINs students to ensure all appear. Aggregate per class with conditional counts and averages, and order by class name.

Pro tip: Explicitly state your assumptions about the schema and business rules (e.g., honors threshold, treatment of missing exams) before writing SQL—this shows you think like a data scientist who validates requirements, not just a coder.

1. Clarify schema and business rules

Ask about table relationships, column names, and definitions (e.g., honors student, pass/fail thresholds, how to handle students with no exams). Confirm whether 'adjusted score' means NULL for below 60 and raw score otherwise.

2. Filter exams to calendar year 2024

Use a CTE to select exams where the exam date falls within 2024 (e.g., EXTRACT(YEAR FROM exam_date) = 2024). This reduces the dataset early.

3. Compute adjusted score and fill NULLs

In a subsequent CTE, compute adjusted_score as NULL if raw score < 60, else raw score. Then create a display_score that replaces NULL with 0 for aggregation purposes.

4. Aggregate per class with LEFT JOIN

LEFT JOIN students to the filtered scores to ensure all students appear. Group by class and compute pass count (score >= 60), fail count (score < 60), honors count (e.g., score >= 90), average adjusted score (using display_score), and distinct exam count.

5. Order and finalize

Order results by class name. Review for edge cases: students with no exams should contribute 0 to counts and averages, and distinct exam count should not double-count.

Key Points to Mention

Use of CTEs for readability and stepwise logic
LEFT JOIN to preserve students with no scores
Conditional aggregation with CASE WHEN for pass/fail/honors counts
Handling NULLs: adjusted score NULL for below 60, then COALESCE to 0 for averaging
Distinct exam count to avoid duplicates if a student takes multiple exams
Assumptions about honors threshold and pass/fail cutoff (e.g., 90 for honors, 60 for pass)

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