CLT and SQL Tasks | Dataford Interview Questions

Explain what CLT is, and solve SQL tasks involving regex and data extraction.

Use PostgreSQL syntax. Parse valid merchant, amount, and authorization-code values from event text, then retain only approved, non-test transactions with matching metadata.

Output

One row per qualifying transaction, with transaction_id, event_timestamp, merchant_name, amount, authorization_code, and source_system.
Exclude malformed events, declined or test transactions, and events without metadata.
Order by event_timestamp ascending, then transaction_id ascending.

Schema

raw_transaction_events

Column	Type	Description
event_idPK	INT	Unique event identifier
transaction_id	VARCHAR(20)	Transaction identifier embedded in the event record
event_text	TEXT	Semi-structured event text containing transaction attributes
event_timestamp	TIMESTAMP	Time when the event was recorded

transaction_metadata

Column	Type	Description
transaction_idPK	VARCHAR(20)	Unique transaction identifier
status	VARCHAR(20)	Transaction processing status
is_test	BOOLEAN	Whether the transaction is a test record
source_system	VARCHAR(30)	System that supplied the transaction metadata

Your question is CLT and SQL Tasks. 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