Using SQL for Student Data | Dataford Interview Questions

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

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

Schema

students

Column	Type	Description
student_idPK	INT	Unique student identifier
first_name	VARCHAR(50)	Student first name
last_name	VARCHAR(50)	Student last name
school_name	VARCHAR(100)	KIPP school attended by the student
grade_level	INT	Student grade level

assessments

Column	Type	Description
assessment_idPK	INT	Unique assessment identifier
subject	VARCHAR(50)	Assessment subject
academic_year	VARCHAR(20)	Academic year of the assessment

assessment_scores

Column	Type	Description
score_idPK	INT	Unique score record identifier
student_id	INT	Student receiving the score
assessment_id	INT	Assessment associated with the score
score	DECIMAL(5,2)	Assessment score, which may be missing

Your question is Using SQL for Student Data. Start with the requirements and the three tables in the Question tab.

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

Problem