Given a table of transactions and a table of chargebacks, write a query to calculate the monthly chargeback rate by payment method on Coinbase.
Use the transaction month as the reporting month. Include payment-method and month combinations with no chargebacks, exclude chargebacks that do not match a transaction, and count each transaction at most once.
month, payment_method, transaction_count, chargeback_count, and chargeback_rate_pctmonth, then payment_method| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| transaction_date | DATE | Date the transaction occurred |
| payment_method | VARCHAR(30) | Payment method used for the transaction |
| amount_usd | DECIMAL(12,2) | Transaction amount in US dollars |
| Column | Type | Description |
|---|---|---|
| chargeback_idPK | INT | Unique chargeback record identifier |
| transaction_id | INT | Transaction associated with the chargeback |
| chargeback_date | DATE | Date the chargeback was recorded |
| reason | VARCHAR(60) | Optional chargeback reason |