Drill 10 of 12 in Education questions
0

Interview Question Bank · Weighted average · lookup join · rows without points

10. Credit-weighted GPA

HardEducationQuestion #226

Concept: Weighted average · lookup join · rows without pointsshow

A GPA is a weighted average: multiply each course's grade points by its credits, add those up, and divide by the credits that earned points. A plain AVG of grade points treats a 2-credit course like a 4-credit one. Grades that carry no points (a withdrawal, or no grade yet) must leave the numerator and the denominator together, while an F stays in both: it's 0 points on real credits.

Example (different data):

SELECT p.name,
  ROUND(SUM(r.score * j.weight) / SUM(j.weight), 2) AS weighted_score
FROM judge_scores r
JOIN judges j ON j.id = r.judge_id
JOIN performers p ON p.id = r.performer_id
WHERE r.score IS NOT NULL
GROUP BY p.id, p.name;

Your task

Brightpath Academy prints a credit-weighted GPA on transcripts. ed_grade_points maps each letter grade to points; a W (withdrawal) carries no points, and enrollments without a letter grade don't count yet.

For every student with at least one letter grade that carries grade points, return student (name), credits (total credits of those courses) and gpa (sum of points × credits, divided by those credits, rounded to 2 decimals). Sort by gpa descending, then student.

Starting Postgres in your browser…

Checking your access…