Interview Question Bank · CROSS JOIN LATERAL (VALUES) · unpivot · FILTER
7. Null-rate profile, one row per column
MediumData engineeringQuestion #247
Concept: CROSS JOIN LATERAL (VALUES) · unpivot · FILTERshow
A profiling report lists each column as its own row, which means unpivoting: turning columns into rows. CROSS JOIN LATERAL (VALUES ('col_a', t.col_a IS NULL), ('col_b', t.col_b IS NULL)) gives every source row one output row per column, and a normal GROUP BY then counts NULLs per column. A UNION ALL of one SELECT per column does the same job with more typing.
Example (different data):
SELECT f.field, COUNT(*) FILTER (WHERE f.missing) AS missing
FROM survey_answers a
CROSS JOIN LATERAL (VALUES ('age', a.age IS NULL), ('city', a.city IS NULL)) AS f(field, missing)
GROUP BY f.field;Your task
Before promoting de_stg_users to production, Fernhill's data platform team profiles it: what share of each column is empty?
Return one row per column (email, phone, country, signup_source, birth_date, referrer_id) with column_name, null_count and null_pct (NULLs as a % of all rows in de_stg_users, rounded to 1 decimal). Sort by null_pct descending, then column_name.
Starting Postgres in your browser…
Checking your access…