Handle Missing Values in SQL

Medium8 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

You have a students table with missing values.

Table: students

id	name	age	department	grade
1	Ana	20	CS	A
2	Raj	NULL	CS	NULL
3	Mei	24	CS	B
4	Sam	30	Math	NULL
5	Zoe	NULL	Math	C

Task

Count how many ages are missing.
Show every student with a missing grade as 'Ungraded'.
Fill each missing age with the average age of the student's department, without deleting any row, and add a flag column that says the age was missing.

Expected output (part 3)

id	name	age_filled	age_was_missing
1	Ana	20	0
2	Raj	22	1
3

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

NULL means unknown. col = NULL is never true, so use IS NULL / IS NOT NULL.
Four options: remove the row, replace with a default (COALESCE), impute (mean, median, previous value), or flag it.
For ML: impute, then keep a missing-indicator column. The fact that a value is missing is often a signal.

1Basic

Count, filter and default

1-- 1. How many ages are missing?2SELECT COUNT(*) - COUNT(age) AS missing_age3FROM students;4 5-- 2. Treat a missing grade as "Ungraded"6SELECT name, COALESCE(grade, 'Ungraded') AS grade7FROM students;8 9-- 3. Keep only complete rows (read-only, nothing is deleted)10SELECT *11FROM students12WHERE age IS NOT NULL;

Downside: dropping rows loses data and can bias the training set

2Optimized

Impute per group and keep a missing flag

1-- Group mean as a window function: every row stays2SELECT id, name,3       COALESCE(age, ROUND(AVG(age) OVER (PARTITION BY department))) AS age_filled,4       CASE WHEN age IS NULL THEN 1 ELSE 0 END                       AS age_was_missing5FROM students;6 7-- Skewed data? Use the median instead of the mean8WITH med AS (9  SELECT department,10         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY age) AS median_age11  FROM students12  GROUP BY department13)14SELECT s.id, s.name, COALESCE(s.age, m.median_age) AS age_filled15FROM students s16LEFT JOIN med m ON m.department = s.department;

Why: keeps every row
the flag becomes a feature
AVG ignores NULLs, so it is the mean of the known ages
Fit imputation values on training data only

Spotted a mistake in these answers?

Report a problem