Rank School Subjects by Average Assessment Score

How would you use SQL to analyze student performance data at KIPP Schools?

Using the provided tables, write a query that summarizes scored assessments by school and subject for the 2024-2025 academic year. Exclude subjects with fewer than two non-null scores and assign each remaining subject a performance rank within its school.

Output

1. One row per school and subject that meets the minimum score requirement
2. Columns: school_name, subject, average_score, assessment_count, and subject_rank
3. Sort by school name, rank ascending, and subject alphabetically

Output details

• Round average_score to two decimal places.
• Rank subjects by average_score descending within each school, using competition ranking for ties.
• Treat tied subjects as sharing a rank, with subsequent ranks skipped.
• Count only non-null scores, exclude null scores from averages, and require at least 2 scores.
• Sort school_name ascending, then subject_rank ascending, then subject alphabetically ascending.