Problem
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.
Requirements
- Use only transactions from
2024-06-01through2024-06-30. - Join transaction data to account and merchant metadata.
- For each dimension value, calculate:
total_transactionsfailed_transactionswherestatus = 'failed'failure_rate = failed_transactions / total_transactions, rounded to 4 decimals
- Return only groups with at least 2 failed transactions.
- Rank rows within each dimension by
failed_transactionsdescending, thentotal_transactionsdescending, thendriver_valueascending.
Schema
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 |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
