Task
You are given transaction data from Intuit Checkout and related account metadata. Write a SQL query to identify the top drivers of failed transactions in the last 30 days. Return one row per driver dimension and driver value, where a driver can come from payment_method, surface_name, country_code, or risk_tier. For each driver value, show total transactions, failed transactions, failure rate, and rank the results by failed transaction count within each driver dimension. Only include driver values with at least 2 failed transactions.
Schema
transactions
| column | type | description |
|---|
| transaction_id | INT | Unique transaction id |
| account_id | INT | Linked account |
| transaction_ts | TIMESTAMP | Transaction timestamp |
| payment_method | VARCHAR(50) | Payment method used |
| status | VARCHAR(20) | Final transaction status |
| failure_reason | VARCHAR(100) | Failure reason if failed |
| amount | NUMERIC(10,2) | Transaction amount |
| | |
accounts
| column | type | description |
|---|
| account_id | INT | Unique account id |
| country_code | VARCHAR(2) | Account country |
| risk_tier | VARCHAR(20) | Risk segment |
| merchant_id | INT | Linked merchant |
| | |
merchants
| column | type | description |
|---|
| merchant_id | INT | Unique merchant id |
| surface_name | VARCHAR(50) | Intuit product surface |
| merchant_name | VARCHAR(100) | Merchant display name |
Sample data
transactions
| transaction_id | account_id | transaction_ts | payment_method | status | failure_reason | amount |
|---|
| 1008 | 8 | 2024-06-29 08:05:00 | ach | failed | insufficient_funds | 120.00 |
| 1002 | 2 | 2024-06-28 10:15:00 | card | failed | do_not_honor | 75.00 |
| 1001 | 1 | 2024-06-27 09:00:00 | card | failed | insufficient_funds | 120.00 |
accounts
| account_id | country_code | risk_tier | merchant_id |
|---|
| 1 | US | high | 101 |
| 2 | US | medium | 102 |
| 11 | NULL | low | 111 |
merchants
| merchant_id | surface_name | merchant_name |
|---|
| 101 | QuickBooks Payments | Alpha Services |
| 102 | Mailchimp | Bright Studio |
| 110 | Credit Karma | Juno Retail |
Expected output
| driver_dimension | driver_value | total_transactions | failed_transactions | failure_rate | failure_rank |
|---|
| country_code | US | 7 | 5 | 0.7143 | 1 |
| payment_method | card | 6 | 4 | 0.6667 | 1 |
| risk_tier | high | 5 | 4 | 0.8000 | 1 |
| surface_name | QuickBooks Payments | 5 | 4 | 0.8000 | 1 |
