SQL Join and Group By Practice

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL Join and Group By Practice. Start with the requirements and the four tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

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

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results

SQL Join and Group By Practice

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL Join and Group By Practice. Start with the requirements and the four tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

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

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results