SQL Transaction Status Verification | Dataford Interview Questions

Write a SQL query to verify that a user transaction was successfully recorded with the correct status flags. Compare each transaction's recorded flags with its expected flags and retain transactions even when no expectation record exists.

Output

One row per transaction, including the transaction ID, user ID, user name, actual flags, expected flags, and verification_status.
Set verification_status to MATCH, MISMATCH, or MISSING_EXPECTATION.
Order by transaction_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	Display name of the user

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User associated with the transaction
recorded_at	TIMESTAMP	Time the transaction was recorded
is_recorded	BOOLEAN	Whether the transaction was recorded
is_successful	BOOLEAN	Whether the transaction succeeded
is_posted	BOOLEAN	Whether the transaction was posted

transaction_status_expectations

Column	Type	Description
transaction_idPK	INT	Transaction being validated
expected_is_recorded	BOOLEAN	Expected recorded flag
expected_is_successful	BOOLEAN	Expected successful flag
expected_is_posted	BOOLEAN	Expected posted flag

Your question is SQL Transaction Status Verification. 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