SQL, data modeling, data quality, EDA, visualization · insight-0040

Decide whether NULL means zero before trusting AVG or COUNT(column)

Situation
An average fare or average delay is computed with AVG over a column that contains NULLs.
Action
For each metric, write what a NULL means (zero, unknown, not applicable). Use COALESCE(col, 0) only when NULL truly means zero, and report COUNT(*) next to COUNT(col) so the coverage is visible.
Reason
Most SQL aggregates ignore NULL inputs and COUNT(*) counts rows; the denominator of AVG silently becomes the non-NULL rows.
Boundary
Revenue, delay, rating and any optional measure in operational data.

Symptom or risk

AVG and COUNT(column) skip NULLs, so the average describes only rows with values; if NULL meant "no revenue" the KPI is overstated.

When NULL means unknown and missingness is unrelated to the value, averaging the observed rows is reasonable if labelled.

Replacing NULL with zero can bias a mean downward just as skipping it can bias it upward; the semantics decide.

Applying COALESCE in a shared view, which hides missingness from every downstream user.

Synthetic airline data: avg_fare is missing more often on intl-long rows in flight_day_raw, so AVG(avg_fare) underweights the highest-fare market.