Schema Risks and Tradeoffs | Dataford Interview Questions

What are some risks with this schema?

Inspect the supplied tables and write a SQL query that identifies transaction records affected by common integrity and data-quality risks. Include records with missing or invalid relationships, inactive or incomplete accounts, invalid amounts, missing currencies, and duplicate external references.

Output

One row per transaction with at least one detected risk
Include the transaction identifier, reference, related identifiers, risk flags, and duplicate-reference count
Sort by transaction_id ascending

Schema

companies

Column	Type	Description
company_idPK	INT	Company identifier
company_name	VARCHAR(100)	Company legal or operating name
status	VARCHAR(20)	Company lifecycle status

accounts

Column	Type	Description
account_idPK	INT	Account identifier
company_id	INT	Owning company identifier
account_name	VARCHAR(100)	Account display name
status	VARCHAR(20)	Account lifecycle status

transactions

Column	Type	Description
transaction_idPK	INT	Transaction identifier
account_id	INT	Account associated with the transaction
external_reference	VARCHAR(50)	Reference supplied by an external system
amount	NUMERIC(12,2)	Transaction amount
currency_code	VARCHAR(3)	Three-letter currency code

Your question is Schema Risks and Tradeoffs. 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