Missing IDs SQL Query | Dataford Interview Questions

Coupang's Rocket Delivery monitoring pipeline compares expected shipment IDs with shipment scan records. table_2 is the authoritative list of expected IDs, while table_1 contains observed scan events and may include duplicates, unmatched IDs, or NULLs.

Write a PostgreSQL query to identify every expected shipment ID that does not appear in the observed scan data.

Requirements

Treat distinct non-NULL IDs in table_2 as the expected population.
Treat an ID as observed when it appears at least once in table_1.
Return each missing ID exactly once and include its expected record count from table_2.
Exclude NULL IDs and IDs that appear only in table_1.
Sort the result by shipment_id in ascending order.

Schema

table_1

Column	Type	Description
scan_idPK	BIGINT	Unique scan event identifier
shipment_id	BIGINT	Shipment ID detected by the scan
scan_status	VARCHAR(20)	Status recorded by the scan

table_2

Column	Type	Description
expected_record_idPK	BIGINT	Unique expected-shipment record identifier
shipment_id	BIGINT	Authoritative expected shipment ID
fulfillment_center	VARCHAR(20)	Coupang fulfillment center

Your question is Missing IDs SQL Query. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem