Top Branch Failure Rate SQL | Dataford Interview Questions

Mercantile Bank wants to identify branches with the highest transaction failure rates over the most recent 30-day reporting period. Write a SQL query to calculate each branch's total transactions and failure percentage for that period.

Output

Return up to five rows, with one row per branch.
Include branch_name, total_transactions, and failure_percentage.
Include branches with no qualifying transactions, but do not allow them to outrank branches with transactions.
Treat only transactions whose status is FAILED as failures. Transactions with other statuses, including NULL, count toward the total but not the failure count.
Sort by failure percentage descending, then total transactions descending, then branch ID ascending. Use a fixed reporting date of 2026-09-26; the 30-day period includes that date and the preceding 29 days.

Schema

branches

Column	Type	Description
branch_idPK	INT	Unique Mercantile Bank branch identifier
branch_name	VARCHAR(100)	Mercantile Bank branch name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
branch_id	INT	Branch where the transaction was processed
transaction_date	DATE	Date the transaction was processed
status	VARCHAR(20)	Transaction outcome, such as SUCCESS or FAILED

Your question is Top Branch Failure Rate SQL. 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