SQL Query Writing for Students

Huron Consulting Group's learning analytics team needs a consolidated performance view for students whose name is exactly xyz. Write one PostgreSQL query that returns every matching student, including students without enrollments or assessment results.

Requirements

1. Use a multi-level CTE pipeline and LAG to evaluate score progression within each student-course history.
2. Calculate each student's total assessment score, treating missing scores as zero.
3. Return distinct enrolled course names as an alphabetically ordered comma-separated list.
4. Return the number of enrolled courses and the number of score improvements over the prior assessment.
5. Preserve matching students with no enrollment through LEFT JOIN operations and order the final output by student_id.

Representative Sample Data

students: (1, 'xyz'), (2, 'xyz'), (3, 'Amina Patel')
enrollments: (1001, 1, 101), (1002, 1, 102), (1003, 3, 103)
courses: (101, 'PostgreSQL Optimization'), (102, 'Data Strategy')
assessments: (5001, 1001, '2026-01-10', 78), (5002, 1001, '2026-02-10', 85), (5003, 1002, '2026-01-15', 91), (5004, 1002, '2026-02-15', NULL)