Project: A Reporting Query

This project is the kind of query a real school asks for every term: who is enrolled, how they are doing, and who needs attention.

The Question

Produce a term report with each student, their level, the number of courses they take, their average score and a status of On track or At risk.

Build it in Steps

Start with enrolment counts, then join scores, then name the final columns. Each CTE should answer one small question.

WITH enrolment_counts AS (
    SELECT student_id, COUNT(*) AS course_count
    FROM enrolments
    GROUP BY student_id
),
score_summary AS (
    SELECT student_id, ROUND(AVG(score), 1) AS average_score
    FROM results
    GROUP BY student_id
)
SELECT s.name,
       s.level,
       COALESCE(ec.course_count, 0) AS courses,
       COALESCE(ss.average_score, 0) AS average_score,
       CASE
           WHEN ss.average_score >= 60 THEN 'On track'
           ELSE 'At risk'
       END AS status
FROM students s
LEFT JOIN enrolment_counts ec ON ec.student_id = s.id
LEFT JOIN score_summary ss ON ss.student_id = s.id
ORDER BY average_score DESC, s.name;

Check the Edges

Test with a student who has no enrolments and one with no scores. COALESCE keeps the report readable instead of showing NULL everywhere.

Practice

1. Add the student email to the report.
2. Change the at-risk threshold to 50.
3. Add a column showing the best score.

Hands-on exercise

Write the full report query.
1. One row per student.
2. Columns: name, level, course count, average score, status.
3. Students with no data must still appear.
4. Sort by average score, highest first.

Recap

Real reports combine joins, aggregates and CTEs. Build them in small steps and test the students with no data.