SQL for Student Analytics | Dataford Interview Questions

Your question is SQL for Student Analytics. Start with the requirements and the three 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.

Problem

Syapse's Precision Medicine Platform needs a state-level education summary to identify regions with the fewest graduates while prioritizing regions with the largest student populations. Write a PostgreSQL query that uses the student, graduate, and education tables.

Requirements

Count distinct students and distinct graduates for each student state.
Include students with no graduate or education record, treating missing graduate records as zero graduates.
Identify every state tied for the minimum graduate count, then return the three states with the most students among that group.
Sort by total students descending, then state ascending to make ties deterministic.

Schema

student

Column	Type	Description
student_idPK	INT	Unique student identifier
student_name	VARCHAR(100)	Student's full name
state	VARCHAR(2)	Two-letter state code

graduate

Column	Type	Description
graduate_idPK	INT	Unique graduation record identifier
student_id	INT	Student associated with the graduation record
graduation_year	INT	Year of graduation

education

Column	Type	Description
education_idPK	INT	Unique education record identifier
student_id	INT	Student associated with the education record
degree	VARCHAR(100)	Degree or educational program
institution_name	VARCHAR(150)	Educational institution name