Average Grade Per Subject

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Average Grade Per Subject. 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.

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

Problem

Adobe Learning Manager stores student assessments by subject. Write a PostgreSQL query that reports the average grade for every subject, considering only grades submitted by active students.

Requirements

Return every subject, including subjects with no qualifying grades.
Exclude inactive students and NULL grade values from the average.
Return the average grade rounded to two decimal places and the count of graded submissions.
Sort the results alphabetically by subject name.

Schema

students

Column	Type	Description
student_idPK	INT	Unique student identifier
student_name	VARCHAR(100)	Student's display name
is_active	BOOLEAN	Whether the student is currently active

subjects

Column	Type	Description
subject_idPK	INT	Unique subject identifier
subject_name	VARCHAR(100)	Subject name

grades

Column	Type	Description
grade_idPK	INT	Unique grade record identifier
student_id	INT	References students.student_id
subject_id	INT	References subjects.subject_id
score	NUMERIC(5,2)	Submitted grade

Tablesstudentssubjectsgrades

Your solutionPostgreSQL

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

Run a query to see results