Drill 7 of 12 in Data engineering questions
0

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…