
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
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:
Requirement B (student-by-subject labels): For every (student, subject) pair with at least one May attempt, return:
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.