Cheat sheets · Module 7
Database Analyst cheat sheet
Audit a messy table, clean it, and prove the fix worked.
COUNT(*) - COUNT(col) · data quality
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.
SELECT warehouse, COUNT(*) AS items, COUNT(*) - COUNT(barcode) AS missing_barcode FROM inventory GROUP BY warehouse;Practice: Missing values per source →
LOWER · TRIM · GROUP BY · HAVING
Hand-typed text rarely matches exactly: ' Ann@X.com' and 'ann@x.com' are the same person. Normalize first, LOWER(TRIM(col)), and group on that expression. NULLs all fall into one group, so drop them before calling anything a duplicate.
SELECT UPPER(TRIM(plate)) AS plate, COUNT(*) AS copies FROM parking_permits WHERE plate IS NOT NULL GROUP BY UPPER(TRIM(plate)) HAVING COUNT(*) > 1;Practice: Duplicates hiding behind case and spaces →
NOT EXISTS · orphan rows · NOT IN and NULL
An orphan is a child row pointing at a parent that doesn't exist, which happens whenever no foreign key enforces the link. Find them with NOT EXISTS or LEFT JOIN ... WHERE parent.key IS NULL. Avoid NOT IN against a column with NULLs: x NOT IN (list with a NULL) is never true, so it silently returns nothing.
SELECT r.id, r.room_code
FROM bookings r
WHERE r.room_code IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM rooms m WHERE m.room_code = r.room_code
);Practice: Orphaned invoices →Validation rules · OR · NULL-safe checks
Data validation is a WHERE clause listing every way a row can be wrong, joined with OR. Each rule needs a NULL check of its own: NULL NOT LIKE '%@%' is NULL, not true, so a missing value slips past a pattern test unless you ask for IS NULL too.
SELECT id, sku, qty, shipped_on FROM shipments WHERE qty <= 0 OR shipped_on > '2024-12-31' OR sku IS NULL OR sku NOT LIKE 'SKU-%';Practice: Payments that break the rules →
TRIM · NULLIF · regexp_replace · ::numeric · ::date
Spreadsheet imports often arrive as all-text columns. To turn text into a number or date: strip the formatting (regexp_replace(x, '[$,]', '', 'g')), turn placeholders like '' or 'N/A' into NULL with NULLIF, then cast with ::numeric or ::date. Casting '' or ' ' fails, so make them NULL first.
SELECT TRIM(city) AS city, NULLIF(regexp_replace(pop_text, '[, ]', '', 'g'), '')::int AS population FROM census_raw;Practice: Parse a text-only import →
UPDATE · INITCAP · TRIM
UPDATE table SET col = expression rewrites a column in place, for every row unless you add WHERE. To fix free-text values, TRIM the stray spaces and INITCAP the casing: INITCAP(' new york ') would be ' New York ' without the TRIM. In an interview, say you'd run the SELECT version first to preview the change.
UPDATE stores SET city = INITCAP(TRIM(city)); SELECT city, COUNT(*) FROM stores GROUP BY city;Practice: Standardize country names →
DELETE … USING · keep the lowest id
To remove duplicates but keep one copy, delete every row that has a twin with the same key and a smaller id: DELETE FROM t a USING t b WHERE a.key = b.key AND a.id > b.id. The first row of each group has no smaller twin, so it survives. Match on the whole key, or you'll delete rows that only look alike.
DELETE FROM reviews a USING reviews b WHERE a.product_id = b.product_id AND a.user_id = b.user_id AND a.id > b.id;Practice: Delete duplicate registrations →
INSERT … ON CONFLICT DO UPDATE · EXCLUDED
An upsert inserts new rows and updates existing ones in one statement: INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col. EXCLUDED is the incoming row that hit the conflict. It needs a unique constraint or primary key on the conflict column, and the incoming key must match it exactly, so clean it in the SELECT.
INSERT INTO stock (warehouse, qty) SELECT UPPER(warehouse), qty FROM stock_counts ON CONFLICT (warehouse) DO UPDATE SET qty = EXCLUDED.qty;Practice: Upsert a supplier price feed →
FULL OUTER JOIN · IS DISTINCT FROM
Reconciling two systems means finding keys missing from either side and keys whose values disagree. FULL OUTER JOIN keeps unmatched rows from both tables, and IS DISTINCT FROM compares values safely: NULL <> 29 is NULL (so the row is missed), but NULL IS DISTINCT FROM 29 is true.
SELECT COALESCE(w.sku, s.sku) AS sku, w.qty, s.qty FROM warehouse_stock w FULL OUTER JOIN store_stock s ON s.sku = w.sku WHERE w.sku IS NULL OR s.sku IS NULL OR w.qty IS DISTINCT FROM s.qty;Practice: Reconcile billing and CRM →
UNIQUE · CHECK · NULL semantics · UNION
ALTER TABLE ... ADD CONSTRAINT fails if any existing row breaks it, so find those rows first. UNIQUE compares exact values and lets any number of NULLs through. A CHECK only fails when its condition is false, so a NULL makes the check pass.
-- Before: ALTER TABLE devices ADD UNIQUE (serial); SELECT id, serial FROM ( SELECT id, serial, COUNT(*) OVER (PARTITION BY serial) AS n FROM devices ) d WHERE serial IS NOT NULL AND n > 1;Practice: Rows that would block a constraint →