Write one SQL for exam scores aggregation
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
You are given an exam database. Write a single SQL statement (CTEs allowed; one final statement only) that satisfies all three requirements below. You must use JOIN, WHERE, GROUP BY, aggregates, and handle NULLs (via ISNULL/COALESCE). Schema and tiny sample data:
Tables
- students(student_id INT, name VARCHAR)
- exams(exam_id INT, subject VARCHAR)
- scores(score_id INT, student_id INT, exam_id INT, attempt_no INT, score INT NULL, taken_at DATE)
Sample rows
students
+------------+-------+
| student_id | name |
+------------+-------+
| 1 | Alice |
| 2 | Bob |
| 3 | Chen |
| 4 | Diego |
+------------+-------+
exams
+---------+---------+
| exam_id | subject |
+---------+---------+
| 10 | Math |
| 20 | English |
+---------+---------+
scores
+----------+------------+---------+------------+-------+------------+
| score_id | student_id | exam_id | attempt_no | score | taken_at |
+----------+------------+---------+------------+-------+------------+
| 100 | 1 | 10 | 1 | 58 | 2025-05-03 |
| 101 | 1 | 10 | 2 | 72 | 2025-05-10 |
| 102 | 2 | 10 | 1 | NULL | 2025-05-07 |
| 103 | 2 | 10 | 2 | 39 | 2025-05-14 |
| 104 | 3 | 10 | 1 | 91 | 2025-05-22 |
| 105 | 4 | 20 | 1 | 84 | 2025-05-05 |
| 106 | 1 | 20 | 1 | 88 | 2025-05-18 |
| 107 | 2 | 20 | 1 | 37 | 2025-05-19 |
| 108 | 3 | 20 | 1 | NULL | 2025-04-29 |
| 109 | 3 | 20 | 2 | 61 | 2025-05-03 |
+----------+------------+---------+------------+-------+------------+
Consider only attempts in May 2025 (2025-05-01 to 2025-05-31 inclusive).
Requirement A (subject-level metrics): Return one row per subject, including subjects with zero May attempts, with these columns:
- subject
- high_scorer_cnt: count of distinct students whose best May score (max per student per subject) is >= 85.
- avg_score_floored: average over all May attempts after transforming score as: t = COALESCE(score, 0); if t < 40 then use 40 else use t.
- low_tag_pct: percentage (0–100, one decimal) of May attempts where COALESCE(score, 0) < 60.
If a subject has zero May attempts, return 0 for the three metrics.
Requirement B (student-by-subject labels): For every (student, subject) pair with at least one May attempt, return:
- subject, student_id, pass_flag where pass_flag = 'Pass' if the student's best May score >= 60 else 'Remedial'.
Requirement C (single statement): Produce a single final result by UNION ALL the two result sets, and add a column level with values 'subject' for Requirement A rows and 'student_subject' for Requirement B rows. For rows where a column is inapplicable, return NULL.
Edge cases to handle: NULL scores, subjects with no May data, students with only NULL or sub-40 attempts, and ensuring distinct students for high_scorer_cnt.
Overview: This question evaluates proficiency in SQL data manipulation and reporting, specifically joins, GROUP BY and aggregate functions, NULL handling (COALESCE/ISNULL), conditional aggregation and set operations to produce combined result sets.
Read the full Capital One Data Scientist interview experience this question came from
You are given an exam database with students, exams (subjects), and exam score attempts.
Write a SINGLE SQL statement (CTEs allowed, but one final statement only) that returns ONE combined result set satisfying all requirements below. You must use JOIN, WHERE, GROUP BY, aggregate functions, and handle NULLs using COALESCE (or ISNULL).
Consider ONLY attempts taken in May 2025 (2025-05-01 through 2025-05-31 inclusive).
Requirement A (subject-level metrics): Return one row per subject (include subjects with zero May attempts) with:
- subject
- high_scorer_cnt: count of DISTINCT students whose best May score (max per student per subject) is >= 85
- avg_score_floored: average over all May attempts after transforming each attempt score as:
t = COALESCE(score, 0);
if t < 40 then use 40 else use t
- low_tag_pct: percentage (0–100, one decimal) of May attempts where COALESCE(score, 0) < 60
If a subject has zero May attempts, return 0 for all three metrics.
Requirement B (student-by-subject labels): For every (student, subject) pair with at least one May attempt, return:
- subject, student_id, pass_flag where pass_flag = 'Pass' if the student's best May score >= 60 else 'Remedial'
Requirement C (single final result): UNION ALL the Requirement A rows and Requirement B rows into one result set and add a column named level:
- level = 'subject' for Requirement A rows
- level = 'student_subject' for Requirement B rows
For columns that don't apply to a row type, return NULL.
Edge cases to handle: NULL scores, subjects with no May data, students with only NULL or sub-40 attempts, and ensuring DISTINCT students for high_scorer_cnt.
Tables
students(student_id INT, name VARCHAR(50))
exams(exam_id INT, subject VARCHAR(50))
scores(score_id INT, student_id INT, exam_id INT, attempt_no INT, score INT, taken_at DATE)
Hints
- Filter May attempts first in a CTE, then build best score per (student, subject) with MAX(COALESCE(score,0)).
- To include subjects with zero attempts, start from exams and LEFT JOIN the filtered attempts.