Rank Drivers of Failed Payments

MediumSQL · PostgreSQL

FinFlow wants to understand which account and transaction attributes are most associated with failed payments in June 2024.

Write a SQL query to return the top-ranked value for each of these dimensions: country_code, payment_method, risk_tier, and surface_name.

Requirements

Use only transactions from 2024-06-01 through 2024-06-30.
For each dimension value, calculate:
- total_transactions
- failed_transactions where status = 'failed'
- failure_rate = failed_transactions / total_transactions, rounded to 4 decimals
Treat missing dimension values as 'Unknown'.
Only keep dimension values with at least 2 failed transactions.
Rank values within each dimension by:
1. failed_transactions descending
2. total_transactions descending
3. driver_value ascending
Return only rows with failure_rank = 1.

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
transaction_ts	TIMESTAMP	Timestamp when the transaction was attempted
payment_method	VARCHAR(50)	Payment method used for the transaction
status	VARCHAR(20)	Final transaction status
failure_reason	VARCHAR(100)	Failure reason when a transaction fails
amount	NUMERIC(10,2)	Transaction amount

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
country_code	VARCHAR(2)	Country code for the account
risk_tier	VARCHAR(20)	Risk segment assigned to the account
merchant_id	INT	Merchant linked to the account

merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
surface_name	VARCHAR(50)	Product surface where the merchant operates
merchant_name	VARCHAR(100)	Merchant display name

Tablestransactionsaccountsmerchants