Compare Tables and Find Missing Rows | Dataford Interview Questions

Your question is Compare Tables and Find Missing Rows. Start with the requirements and the two tables on the right.

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

Problem

The Hanover Insurance Group needs to reconcile a policy administration feed with its daily warehouse snapshot. Write a PostgreSQL query that identifies policies present in only one of the two sources.

Requirements

Match records using policy_number.
Return policies missing from either hanover_policy_feed or hanover_policy_snapshot.
Show the policy number, discrepancy classification, and available status and line-of-business values from each source.
Exclude policies that exist in both tables, even when their status values differ.
Order the results by policy_number and then discrepancy.

Schema

hanover_policy_snapshot

Column	Type	Description
policy_numberPK	VARCHAR(20)	Unique policy identifier in the warehouse snapshot
policy_status	VARCHAR(20)	Policy status captured by the snapshot
line_of_business	VARCHAR(40)	Insurance line associated with the policy

hanover_policy_feed

Column	Type	Description
policy_numberPK	VARCHAR(20)	Unique policy identifier received from the source feed
policy_status	VARCHAR(20)	Policy status received from the source feed
line_of_business	VARCHAR(40)	Insurance line received from the source feed