Lesson 11 of 13 · 20 min
Profiling and handling missing data
By the end of this lesson you can profile missing values per column and per group, distinguish missing-by-design from missing-by-mistake, and choose an honest treatment (exclude, flag, or fill) that you can explain in a report.
Handling missing data starts with measuring it. The basic tool is the difference between two counts: count(*) counts rows, count(column) counts non-NULL values, so count(*) - count(column) is the number of missing values. Divided by count(*) it is the missing rate.
Then ask why each gap exists. There are two very different kinds:
- Structural (missing by design): the value cannot exist. A no-show has no consultation start time; a self-pay patient has no insurer; a consumer account has no company name. These NULLs are correct, and replacing them would be wrong.
- Defects (missing by mistake): the value should exist but was not recorded. A completed visit without a check-in time, a customer without a city. These lower the quality of any metric that needs the value.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons