Interview Question Bank · COUNT(*) OVER (PARTITION BY) · every version · NULL keys
1. Duplicate keys in staging
EasyData engineeringQuestion #241
Concept: COUNT(*) OVER (PARTITION BY) · every version · NULL keysshow
GROUP BY key HAVING COUNT(*) > 1 tells you which keys are duplicated, but it collapses each key to one row. To see every version side by side, put COUNT(*) OVER (PARTITION BY key) on each row and filter on it in an outer query. Watch out for NULL keys: PARTITION BY and GROUP BY put all NULLs in one group, so they look like duplicates of each other.
Example (different data):
SELECT * FROM ( SELECT b.*, COUNT(*) OVER (PARTITION BY isbn) AS copies FROM book_imports b WHERE isbn IS NOT NULL ) t WHERE copies > 1;
Your task
Fernhill's nightly order load failed its primary-key check on de_stg_orders. Before deciding which version to keep, the data platform team wants to see every row involved.
Return every staging row whose order_id appears more than once: order_id, stg_row_id, amount, source_file, extracted_at and copies (how many rows share that order_id). Rows with a NULL order_id are a separate problem, so leave them out. Sort by order_id, then extracted_at, then stg_row_id.
Starting Postgres in your browser…
Checking your access…