Top 3 Engaged Students Per Institution | Dataford Interview Questions

Write a SQL query to identify the top 3 most engaged students per institution using window functions. Use the provided institutions, students, and engagement_events tables. Define engagement as the sum of engagement_points, treating missing points as zero.

Output

One row per qualifying student, with institution_id, institution_name, student_id, student_name, engagement_score, and engagement_rank
Include up to three students per institution, ranked by score descending, with lower student_id first for ties
Order by institution_id, then engagement_rank

Schema

institutions

Column	Type	Description
institution_idPK	INT	Unique institution identifier
institution_name	VARCHAR(100)	Institution name

students

Column	Type	Description
student_idPK	INT	Unique student identifier
institution_id	INT	Institution attended by the student
student_name	VARCHAR(100)	Student name

engagement_events

Column	Type	Description
event_idPK	INT	Unique engagement event identifier
student_id	INT	Student associated with the event
event_type	VARCHAR(50)	Type of engagement activity
engagement_points	INT	Points assigned to the event

Your question is Top 3 Engaged Students Per Institution. 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