SQL Join and Group By Practice

HardSQL · PostgreSQL

Problem

The College Board needs a monthly view of SAT performance across test centers. Write a PostgreSQL query that combines registration, student, test center, and score data to identify centers with at least two scored, confirmed registrations per month.

Requirements

Use INNER JOIN operations to combine the related tables.
Consider only confirmed registrations from 2025 and use the latest score report for each registration.
Group results by test center and registration month, returning registered count, scored count, and average total score.
Use HAVING to retain groups with at least two scored registrations.
Use LAG to show the previous month's average score and the change from that month.
Briefly explain when a join is preferable to a subquery, and mention how a subquery or CTE could isolate the latest score report.

Schema

students

Column	Type	Description
student_idPK	INT	Student identifier
student_name	VARCHAR(100)	Student name
school_name	VARCHAR(150)	Student school
grade_level	INT	Current grade level

registrations

Column	Type	Description
registration_idPK	INT	Registration identifier
student_id	INT	Registered student
center_id	INT	Selected test center
registered_at	DATE	Registration date
status	VARCHAR(20)	Registration status

test_centers

Column	Type	Description
center_idPK	INT	Test center identifier
center_name	VARCHAR(120)	Test center name
region	VARCHAR(50)	Geographic region

score_reports

Column	Type	Description
report_idPK	INT	Score report identifier
registration_id	INT	Associated registration
report_date	DATE	Score publication date
total_score	INT	Combined SAT score

Tablesstudentsregistrationstest_centersscore_reports