SQL for Student Subject Totals | Dataford Interview Questions

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

American Airlines training analytics tracks student performance across multiple subjects. Write a PostgreSQL query to identify students whose sum of all recorded subject scores is greater than 200.

Requirements

Join students with subject_scores using the student identifier.
Calculate each student's total score across all recorded subjects.
Return the student ID, student name, and total score only for totals greater than 200.
Sort the results by total score descending, then student ID ascending. Treat NULL scores as contributing zero, and exclude students with no matching score records.

Schema

students

Column	Type	Description
student_idPK	INTEGER	Unique student identifier
student_name	VARCHAR(100)	Student's full name

subject_scores

Column	Type	Description
score_idPK	INTEGER	Unique score record identifier
student_id	INTEGER	Student associated with the score
subject_code	VARCHAR(20)	Training subject code
score	INTEGER	Subject score