Drill 1 of 10 in Database Analyst
0

Database Analyst · COUNT(*) - COUNT(col) · data quality

1. Missing values per source

Concept: COUNT(*) - COUNT(col) · data qualityshow

COUNT(*) counts rows, but COUNT(col) counts only rows where col is not NULL. So COUNT(*) - COUNT(col) is the number of missing values, and it's 0 (not NULL) when nothing is missing. COUNT(*) FILTER (WHERE col IS NULL) gives the same answer.

Example (different data):

SELECT warehouse,
  COUNT(*) AS items,
  COUNT(*) - COUNT(barcode) AS missing_barcode
FROM inventory
GROUP BY warehouse;

Your task

Before migrating the CRM, the data team wants to know which intake channel produces the most incomplete contact records.

For each source in crm_contacts, return source, contacts (all rows), missing_email, missing_phone and missing_country (rows where that column is NULL). Sort by source.

Starting Postgres in your browser…

Checking your access…