Module 03 Activity
Scenario
A colleague reports "average rating is 2.7, we have a problem". Another reports 3.05 from the same table. Neither is lying. You are going to find out why, and build the habit that stops it happening again.
Task
For each of customers.country, feedback.rating, and tests.score:
- Count the total rows.
- Count the non-NULL values.
- Compute the coverage percentage.
- Compute the average two ways - once letting SQL skip NULLs, and once with
COALESCE(col, 0). - Decide which of the two is defensible for that column, and write one sentence saying why.
Deliverable
A coverage table with columns column | rows | present | coverage % | avg (nulls skipped) | avg (nulls as 0) | which one I would publish and why.
Check your work
| Column | Rows | Present | Coverage | Skipped | As zero |
|---|---|---|---|---|---|
customers.country | 4,812 | 4,512 | 93.8% | - | - |
feedback.rating | 500 | 440 | 88.0% | 3.05 | 2.68 |
tests.score | 10 | 8 | 80.0% | 76.25 | - |
Both colleagues in the scenario were computing the feedback average: 3.05 skipping the 60 missing ratings, 2.68 treating them as zero.
For feedback.rating, 3.05 is the defensible figure, because a missing rating means "this person did not rate", not "this person rated it zero" - and on a 1-to-5 scale, zero is not even a possible response. Publish it as "3.05 across the 440 responses that included a rating (88% of 500)".
tests.score shows why coverage matters more than the average: 8 scores is a small enough base that one more result would move the mean noticeably. Report the base alongside the number every time.
The habit
A number without its denominator is not an answer. Whenever you report an average, report how many rows it was actually computed over - not how many rows the table has.