SQL NULL and COUNT practice: report the denominator

What you will practise

• Explain why an unknown duration is not a zero-minute duration.
• Write and execute COUNT, IS NULL and AVG queries on a disposable SQLite table.
• Distinguish event-row totals from unique-ticket counts.
• Report missingness and exclusions alongside a result.

Start with the business question

A fictional service team asks: How long did resolved tickets take in the supplied extract? Before writing SQL, define a ticket, a resolved row, a known duration and the period covered. The dataset below is a selected training extract, not a representative sample. It cannot establish service quality, employee performance or a population trend.

The extract has eight rows. One ticket has two recorded rows, so row count and distinct ticket count answer different questions. The exercise deliberately retains that duplication until you investigate it. A source event identifier is not automatically a stable ticket identifier.

Unknown is a separate state

NULL marks an absent SQL value. Here it means the duration has not been supplied; it does not prove that resolution took no time. A recorded 0 is a real value in this fictional schema. Keep those states separate until the source owner explains how durations were captured.

SQLite COUNT(*) counts selected rows, whereas COUNT(minutes) counts selected non-NULL minute values. AVG(minutes) uses the non-NULL values. These SQL behaviors do not choose a business denominator for you; that choice still needs a written definition.

Use minutes IS NULL to identify absent durations. The predicate minutes = NULL does not provide the intended missing-value filter. Conditional counting can describe missingness without replacing unknown values.

When a stakeholder asks for the average, ask whether the target is event rows, unique tickets, all resolved tickets, or only resolved tickets with a known duration. These populations can differ. Do not use a convenient SQL function as a substitute for that question. Add the selected-row count and known-duration count to the same result so a reader can see the exclusion instead of discovering it later.

Inspect repeated entities before deduplicating

T7 appears in E7 and E8. They may be duplicate collection, revisions, or separate measurements. This case supplies identical fields but does not supply a collection contract that proves which explanation applies. Do not delete an event merely because its ticket repeats.

If the question concerns unique tickets, COUNT(DISTINCT ticket_id) gives seven for this extract. That is not a valid way to average durations: AVG(DISTINCT minutes) would remove equal numeric durations even when they belong to different tickets. Establish one justified record per ticket before computing a ticket-level average.

The supplied result is a row-based descriptive result. Label it that way and disclose the repeated T7 rows. A future ticket-level report requires an explicit deduplication rule and a validation of the chosen record.

Keep a small query reproducible

Run the supplied SQL only in an empty disposable SQLite database. It creates a fictional support_events table and never connects to READY or a provider. You may also calculate the results by hand. No paid account or actual customer data is needed.

Keep the input extract, query, scope and assumptions together. If a dashboard number changes, compare definitions as well as data. A more precise decimal is not a substitute for a justified denominator. A mean alone also hides the distribution and missingness.

The exercise supports the course’s Prepare the Evidence, Query with SQL and Interpret the Result modules. It is supplementary practice, not an installed replacement for their questions or historical results.

Worked example: read one aggregate result

The query selects seven resolved rows. Six have a supplied duration, one has an unknown duration. The known values total 108 minutes; the mean is 108/6 = 18 minutes. Known-duration coverage is 6/7, about 85.7%, among resolved rows. Neither denominator represents unique tickets.

Replacing the missing duration with zero produces 108/7, about 15.43 minutes. That is an imputation assumption, not an observed mean. Excluding the genuinely recorded zero produces 108/5 = 21.6 minutes, answering a different selected question. Neither substitution should be silent.

A defensible sentence is: In this supplied fictional extract, six of seven resolved event rows have a duration; their row-based mean is 18 minutes. One resolved duration is unknown and ticket T7 appears twice. We have not estimated a unique-ticket or population average.

SELECT COUNT(*) AS resolved_rows, COUNT(minutes) AS known_rows, SUM(CASE WHEN minutes IS NULL THEN 1 ELSE 0 END) AS missing_rows, AVG(minutes) AS mean_known_minutes FROM support_events WHERE resolved = 1;

Put it into practice

Question and scope:
Unit of analysis:
Inclusion rule:
Numerator/denominator:
Observed result:
Missingness/repeated entities:
Unsupported conclusions:
Next verification:

Download the practice files

• fictional-support-events.sql
• result-template.txt

Review your reasoning

Try the exercise first, then open these self-review hints. They are guidance, not an assessment result.

Independent learning, clear boundaries

Original independent READY learning. Not official Google or Coursera training, not endorsed by either provider, and not a Google Professional Certificate, READYScore exam, certification or credited course completion. All case data are fictional. Writing is self-reviewed, not automatically graded. This public resource has no account save, submission or reward function.

These exercises use fictional examples. They do not replace production authorization, provider credentials, professional advice or certification requirements.

Official references

• SQLite aggregate functions

  COUNT(*) versus COUNT(value), AVG non-NULL behavior only; fictional data, calculations and business interpretation are original.

• SQLite expressions

  NULL comparison and IS semantics only; exercise design is original.