Monthly Transactions III
Stripe
Problem Statement
Stripe operates a global payment processing network. A key metric for risk management is the chargeback rate and value relative to approved transactions.
A chargeback happens when a customer disputes a transaction after it has occurred. Even if a transaction was approved in a previous month, a chargeback is recorded based on the date it was filed.
Write an SQL query to find for each month and country:
- The number of approved transactions and their total amount.
- The number of chargebacks and their total amount.
Rules:
- Output columns: month, country, approved_count, approved_amount, chargeback_count, chargeback_amount.
- month should be in YYYY-MM format.
- Only include months where there is at least one approved transaction or one chargeback.
- Results must be sorted by month in ascending order, then by country in ascending order.
Tests your understanding of
Union, Aggregation and Date Formatting
Input Tables
Transactions
| id(INTEGER) | country(VARCHAR) | state(VARCHAR) | amount(INTEGER) | trans_date(DATE) |
|---|---|---|---|---|
| 101 | US | approved | 1000 | 2019-05-18 |
| 102 | FR | declined | 2000 | 2019-05-19 |
| 103 | US | approved | 3000 | 2019-06-10 |
| 104 | US | approved | 4000 | 2019-06-13 |
| 105 | FR | approved | 5000 | 2019-06-15 |
Chargebacks
| trans_id(INTEGER) | trans_date(DATE) |
|---|---|
| 102 | 2019-05-29 |
| 101 | 2019-06-30 |
| 105 | 2019-09-18 |
Merchants
| merchant_id(INTEGER) | merchant_name(VARCHAR) | category(VARCHAR) |
|---|---|---|
| 1 | Apple | Tech |
| 2 | Stripe Shop | Retail |
| 3 | Bakery | Food |
| 4 | Gym | Health |
| 5 | Airline | Travel |
| 6 | Hotel | Travel |
Expected Output
| month(VARCHAR) | country(VARCHAR) | approved_count(INTEGER) | approved_amount(INTEGER) | chargeback_count(INTEGER) | chargeback_amount(INTEGER) |
|---|---|---|---|---|---|
| 2019-05 | US | 1 | 1000 | 0 | 0 |
| 2019-06 | FR | 1 | 5000 | 0 | 0 |
| 2019-06 | US | 2 | 7000 | 1 | 1000 |
| 2019-09 | FR | 0 | 0 | 1 | 5000 |
Tags
MediumUnionAggregationDate Formatting
20-25 min
58%