Drill 1 of 12 in Data engineering questions
0

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…