A missing value and a recorded zero answer different questions. Before you build a dashboard, decide whether each number represents an observation, an unknown value or something that does not apply. This beginner exercise shows how that decision changes an average.
The records below are fictional practice data, not ScholarLogic learner or business results. You can use a local SQL playground; no customer information is needed.
The small dataset
Imagine five service requests. The column response_minutes records how long the first response took. A zero means an immediate response was recorded. A NULL means no duration was recorded; it does not tell us why.
| Request | Response minutes | Interpretation |
|---|---|---|
| 1 | 10 | Recorded response |
| 2 | 20 | Recorded response |
| 3 | NULL | Duration unknown |
| 4 | 0 | Immediate response recorded |
| 5 | 30 | Recorded response |
Run the baseline query
WITH requests(id, response_minutes) AS (
VALUES (1, 10), (2, 20), (3, NULL), (4, 0), (5, 30)
)
SELECT COUNT(*) AS total_requests,
COUNT(response_minutes) AS recorded_durations,
AVG(response_minutes) AS average_recorded_minutes
FROM requests;Expected result: five requests, four recorded durations and an average of 15 minutes. PostgreSQL’s aggregate-function documentation specifies that COUNT(*) counts rows, while COUNT(column) and AVG(column) operate on non-null values. This query uses PostgreSQL-compatible syntax; check the syntax and numeric return type in your own database.
Why replacing NULL with zero changes the story
The recorded durations total 60 minutes. Dividing by four gives 15. If you treat the missing duration as zero, you divide the same 60 by five and report 12. Both calculations can be executed successfully, but they answer different questions. The second silently assumes the missing request received an immediate response.
Before replacing a missing value, ask the data owner what it means. Was a response never sent? Was a timestamp lost during import? Was the request cancelled? These cases deserve different treatment. A clean-looking number is not a substitute for a definition.
Make the dashboard explain its denominator
- Label the metric “Average response time for recorded durations”.
- Show coverage alongside it: four of five requests have a duration, or 80%.
- Keep a separate count of missing durations for investigation.
- Record whether cancelled requests belong in the metric.
A useful portfolio screenshot shows the average, coverage and a short definition together. Include the five source rows and the SQL in your project notes so a reviewer can reproduce your result.
Three checks to add to your exercise
- Replace the missing duration with 40. The total becomes 100 and the average becomes 20 across five recorded durations.
- Make every duration NULL. In PostgreSQL, the recorded count becomes zero and the average is NULL. Decide how your interface should explain that unavailable result.
- Add a sixth request with a recorded zero. The original 60-minute total divided by five recorded durations becomes 12. This time the lower average follows an actual recorded observation.
Can AI help explain the query?
Ask an AI assistant to calculate the denominator before producing SQL. Compare its explanation with your hand calculation, then run the query. If it suggests filling every blank with zero, ask which business assumption supports that replacement. Keep real customer records and private contact details out of the prompt.
Continue practising
Next, connect this check to a small SQL-to-Power-BI dashboard exercise. If you want classroom guidance in BTM Layout, review ScholarLogic’s five-month Data Analytics course and request a demo. The team confirms availability; this exercise does not promise a job outcome.
Prepared with AI assistance for ScholarLogic. All example records are fictional.
