Your question is Rank Drivers of Failed Payments. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
total_transactionsfailed_transactions where status = 'failed'failure_rate = failed_transactions / total_transactions, rounded to 4 decimals'Unknown'.failed_transactions descendingtotal_transactions descendingdriver_value ascendingfailure_rank = 1.| 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 |
| 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 |
| 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 |