Ensuring Data Accuracy | Dataford Interview Questions

How do you ensure accuracy when working with sensitive financial or liability data?

Write a SQL query using the policies, liability_transactions, and ledger_entries tables to identify policies with missing data, null financial amounts, or differences between posted liability and ledger totals. Treat differences of 0.01 or less as accurate.

Output

One row per policy requiring review.
Return policy_id, liability_total, ledger_total, variance, and review_status.
Order by the absolute variance descending, then policy_id ascending.

Schema

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
policy_status	VARCHAR(20)	Current policy status
product_name	VARCHAR(60)	Policy product name

liability_transactions

Column	Type	Description
transaction_idPK	INT	Unique liability transaction identifier
policy_id	INT	Related policy identifier
transaction_type	VARCHAR(30)	Type of liability transaction
amount	DECIMAL(14,2)	Transaction amount
transaction_status	VARCHAR(20)	Posting status

ledger_entries

Column	Type	Description
entry_idPK	INT	Unique ledger entry identifier
policy_id	INT	Related policy identifier
amount	DECIMAL(14,2)	Ledger amount
entry_status	VARCHAR(20)	Ledger posting status
source_system	VARCHAR(30)	Originating finance system

Your question is Ensuring Data Accuracy. Start with the requirements and the three 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