Thinking habit: Know what you don't know
AVG with NULL values in SQL (and COALESCE)
The question: Find the class average for the test. Students who didn't take it (score is NULL) count as 0.
scores
| student | score |
|---|---|
| Asha | 80 |
| Ravi | 60 |
| Neha | NULL |
| Kabir | 100 |
| Meera | NULL |
This query looks right. Run it, decide if it is, and fix it.
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT AVG(score) AS class_average FROM scores;
AVG ignores NULLs, so it averages only the 3 students who took the test (80). The question counts all 5, with 0 for the two who didn't.
The answer
SELECT AVG(COALESCE(score, 0)) AS class_average FROM scores;
COALESCE(score, 0) turns a missing score into 0 before averaging, so all 5 students count: 240 / 5 = 48.
Neither answer is always right. Say it out loud in an interview: "Should missing values count as zero, or be left out?" That question is half the answer.
SUM divided by COUNT(*)
SELECT 1.0 * SUM(score) / COUNT(*) AS class_average FROM scores;
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.