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
- Use a multi-level CTE pipeline and
LAG to evaluate score progression within each student-course history.
- Calculate each student's total assessment score, treating missing scores as zero.
- Return distinct enrolled course names as an alphabetically ordered comma-separated list.
- Return the number of enrolled courses and the number of score improvements over the prior assessment.
- 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)
