SQL Transaction Failure Drivers | Dataford Interview Questions

Write a SQL query to analyze transaction failures by day, channel, and failure reason, and identify the top drivers of errors at Airtel Payments Bank.

Use the provided transaction, channel, and failure-reason tables. Include failed transactions only and treat missing failure reasons or unknown channels explicitly.

Output

One row per failure date, channel, and failure reason.
Return failure_date, channel, failure_reason, daily_failure_count, channel_reason_total, reason_rank, and is_top_driver.
Rank reasons within each channel by total failures, mark the top two as drivers, and order by date, channel, and reason.

Schema

transaction_events

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
occurred_at	TIMESTAMP	Transaction timestamp
channel_id	INT	Payment channel identifier
status	VARCHAR(20)	Transaction status
failure_reason_code	VARCHAR(30)	Failure reason code

payment_channels

Column	Type	Description
channel_idPK	INT	Unique channel identifier
channel_name	VARCHAR(50)	Payment channel name

failure_reasons

Column	Type	Description
reason_codePK	VARCHAR(30)	Unique failure reason code
reason_description	VARCHAR(100)	Readable failure reason

Your question is SQL Transaction Failure Drivers. 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