Write one SQL for exam scores aggregation

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.

Exam scores aggregation with subject metrics + student labels (single statement UNION ALL)

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.

Table students

column	type	constraints
student_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL

Sample data:

name	student_id
Alice	1
Bob	2
Chen	3
Diego	4

Table exams

column	type	constraints
exam_id	INT	PRIMARY KEY, NOT NULL
subject	VARCHAR(50)	NOT NULL

Sample data:

exam_id	subject
10	Math
20	English
30	Science

Table scores

column	type	constraints
score_id	INT	PRIMARY KEY, NOT NULL
student_id	INT	NOT NULL
exam_id	INT	NOT NULL
attempt_no	INT	NOT NULL
score	INT
taken_at	DATE	NOT NULL

Sample data:

attempt_no	exam_id	score	score_id	student_id	taken_at
1	10	58	100	1	2025-05-03
2	10	72	101	1	2025-05-10
1	10	NULL	102	2	2025-05-07
2	10	39	103	2	2025-05-14
1	10	91	104	3	2025-05-22
1	20	84	105	4	2025-05-05
1	20	88	106	1	2025-05-18
1	20	37	107	2	2025-05-19
1	20	NULL	108	3	2025-04-29
2	20	61	109	3	2025-05-03

Expected output

A single UNION ALL result set with columns: level, subject, high_scorer_cnt, avg_score_floored, low_tag_pct, student_id, pass_flag. Rows at level='subject' contain subject-level metrics (student_id/pass_flag are NULL). Rows at level='student_subject' contain student labels (metrics are NULL). Subjects with zero May attempts still appear with metrics = 0.

avg_score_floored	high_scorer_cnt	level	low_tag_pct	pass_flag	student_id	subject
68.3	1	subject	25.0	NULL	NULL	English
60.2	1	subject	60.0	NULL	NULL	Math
40.0	0	subject	0.0	NULL	NULL	Science
NULL	NULL	student_subject	NULL	Pass	1	English
NULL	NULL	student_subject	NULL	Remedial	2	English
NULL	NULL	student_subject	NULL	Pass	3	English
NULL	NULL	student_subject	NULL	Pass	4	English
NULL	NULL	student_subject	NULL	Pass	1	Math
NULL	NULL	student_subject	NULL	Remedial	2	Math
NULL	NULL	student_subject	NULL	Pass	3	Math

Hints

1. Filter May attempts first in a CTE, then build best score per (student, subject) with MAX(COALESCE(score,0)).
2. To include subjects with zero attempts, start from exams and LEFT JOIN the filtered attempts.
3. For low_tag_pct, use SUM(CASE...) / NULLIF(COUNT(*),0) and COALESCE the final result to 0.