SQL for Branch KPIs | Dataford Interview Questions

Write a SQL query to analyze branch-level transaction volumes, failed transactions, and average turnaround time by month.

Use the provided branch and transaction data. Include only transactions associated with a known branch, and calculate turnaround time from initiation to completion.

Output

One row per branch and calendar month
Columns: branch_code, branch_name, month, total_transactions, failed_transactions, and avg_turnaround_minutes
Include failed transactions in the total count, count failed transactions separately, and ignore incomplete turnaround times when calculating the average
Order by branch_code, then month ascending

Schema

branches

Column	Type	Description
branch_codePK	INT	Unique branch identifier
branch_name	VARCHAR(100)	Official branch name
city	VARCHAR(80)	City where the branch operates

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
branch_id	INT	Branch that processed the transaction
status	VARCHAR(20)	Transaction processing status
initiated_at	TIMESTAMP	Transaction initiation timestamp
completed_at	TIMESTAMP	Transaction completion timestamp

Your question is SQL for Branch KPIs. 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