Intuit's payments team 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 driver values across four dimensions: payment_method, surface_name, country_code, and risk_tier.
2024-06-01 through 2024-06-30.total_transactionsfailed_transactions where status = 'failed'failure_rate = failed_transactions / total_transactions, rounded to 4 decimalsfailed_transactions descending, then total_transactions descending, then driver_value ascending.transactions| column | type | description |
|---|---|---|
| transaction_id | INT | Unique transaction ID |
| account_id | INT | Account tied to the transaction |
| transaction_ts | TIMESTAMP | Attempt timestamp |
| payment_method | VARCHAR(50) | Payment method used |
| status | VARCHAR(20) | Transaction status |
| failure_reason | VARCHAR(100) | Failure reason for failed transactions |
| 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) | Product surface |
| merchant_name | VARCHAR(100) | Merchant display name |
| 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 |
| 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 |
| account_id | country_code | risk_tier | merchant_id |
|---|---|---|---|
| 8 | US | high | 107 |
| 2 | US | medium | 102 |
| 1 | US | high | 101 |
| 5 | US | low | 105 |
| 3 | CA | low | 103 |
| 6 | US | high | 101 |
| 7 | CA | low | 106 |
| 9 | GB | medium | 108 |
| 10 | IN | 109 | |
| 11 | low | 111 | |
| 12 | US | high | |
| 4 | GB | medium | 104 |
| 1 | FR | standard | 71 |
| 2 | BR | new | 49 |
| 3 | PT | startup | 59 |
| 4 | US | high | 47 |
| 5 | MX | fraud_sensitive | 5 |
| 6 | PH | high | 70 |
| 7 | CA | seasonal | 31 |
| 8 | IT | internal | 66 |
| 9 | null | inactive | 47 |
| 10 | AE | Unknown | 3 |
| 11 | NO | null | 92 |
| 12 | CA | fraud_sensitive | 40 |
| 13 | null | inactive | 98 |
| 14 | SG | recovering | 91 |
| 15 | AE | null | 79 |
| merchant_id | surface_name | merchant_name |
|---|---|---|
| 107 | QuickBooks Payments | Foxtrot Parts |
| 102 | Mailchimp | Bright Studio |
| 101 | QuickBooks Payments | Alpha Services |
| 103 | TurboTax | North Retail |
| 104 | QuickBooks Online | West Health |
| 105 | Credit Karma | Delta Goods |
| 106 | Mailchimp | Echo Media |
| 108 | TurboTax | Gamma Travel |
| 109 | Helix Labs | |
| 110 | Credit Karma | Juno Retail |
| 111 | ProConnect | Lattice Works |
| 112 | QuickBooks Payments | Kite Supply |
| 1 | QuickBooks Money | Lattice Works |
| 2 | TurboTax Business | Maple Foods |
| 3 | null | Maple Foods |
| 4 | QuickBooks Desktop | Cedar Home |
| 5 | QuickBooks Desktop | Atlas Repair |
| 6 | Mailchimp Forms | Pioneer Dental |
| 7 | QuickBooks Online | Bright Studio |
| 8 | QuickBooks Payroll | Summit Legal |
| 9 | Mailchimp Forms | Delta Goods |
| 10 | QuickBooks POS | Pioneer Dental |
| 11 | Mint | Gamma Travel |
| 12 | QuickBooks Online | Bright Studio |
| 13 | QuickBooks Payroll | Harbor Fitness |
| 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 |
| 1005 | 5 | 2024-06-25 13:45:00 | wallet | succeeded | 40.00 | |
| 1003 | 3 | 2024-06-24 11:30:00 | ach | succeeded | 200.00 | |
| 1004 | 1 | 2024-06-22 16:20:00 | card | failed | expired_card | 55.00 |
| 1006 | 6 | 2024-06-20 17:10:00 | card | failed | processor_error | 90.00 |
| 1007 | 7 | 2024-06-18 12:00:00 | card | succeeded | 30.00 | |
| 1009 | 9 | 2024-06-15 14:00:00 | wallet | failed | authentication_failed | 60.00 |
| 1010 | 10 | 2024-06-12 15:30:00 | failed | unknown_method | 45.00 | |
| 1011 | 11 | 2024-06-10 09:40:00 | card | pending | 80.00 | |
| 1012 | 12 | 2024-05-28 10:00:00 | ach | failed | insufficient_funds | 110.00 |
| 1 | 5 | 2024-06-30 12:52:56 | ach | captured | pickup_card | 169 |
| 2 | 2 | 2024-06-22 12:27:59 | amex | expired | duplicate_transaction | 51 |
| 3 | 90 | 2024-06-27 20:13:27 | wallet | processing | null | 211 |
| 4 | 1 | 2024-06-03 04:53:38 | wallet | queued | card_not_supported | 102 |
| 5 | 37 | 2024-06-09 21:09:21 | wire | approved | card_not_supported | 170 |
| 6 | 8 | 2024-06-21 18:13:51 | check | succeeded | null | 137 |
| 7 | 50 | 2024-06-05 15:59:44 | afterpay | voided | invalid_account | 150 |
| 8 | 97 | 2024-06-16 05:35:14 | afterpay | reversed | currency_not_supported | 129 |
| 9 | 72 | 2024-06-11 13:17:46 | manual_invoice | failed | pickup_card | 225 |
| 10 | 96 | 2024-05-30 02:45:10 | Unknown | reversed | restricted_card | 123 |
| 11 | 42 | 2024-06-15 18:30:25 | venmo | queued | invalid_expiry | 165 |
| 12 | 71 | 2024-06-29 13:55:29 | discover | voided | insufficient_funds | 113 |
| 13 | 90 | 2024-05-26 12:51:06 | upi | partially_refunded | account_closed | 180 |
| 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 |
