Monthly Transactions II
Stripe
Problem Statement
Stripe needs a monthly consolidated report. You are given two tables: transactions (all initial payment attempts) and chargebacks (disputed payments).
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:
- A Chargeback event belongs to the month it happened (from the chargebacks table), not the month of the original transaction.
- If a month/country pair has no approved transactions and no chargebacks, do not include it.
- Return columns: month, country, approved_count, approved_amount, chargeback_count, chargeback_amount.
- Results must be sorted by month in ascending order.
Tests your understanding of
Basic SQL, UNION ALL, Aggregation and Date Manipulation
Input Tables
transactions
| id(INTEGER) | country(VARCHAR) | state(VARCHAR) | amount(INTEGER) | trans_date(DATE) |
|---|---|---|---|---|
| 101 | US | approved | 1000 | 2019-05-18 |
| 102 | US | declined | 2000 | 2019-05-19 |
| 103 | US | approved | 3000 | 2019-06-10 |
| 104 | US | approved | 4000 | 2019-06-13 |
| 105 | IN | approved | 5000 | 2019-06-15 |
| 106 | IN | declined | 100 | 2019-06-20 |
| 107 | UK | approved | 500 | 2019-07-01 |
chargebacks
| trans_id(INTEGER) | trans_date(DATE) |
|---|---|
| 102 | 2019-05-29 |
| 101 | 2019-06-30 |
| 105 | 2019-09-18 |
| 107 | 2019-07-15 |
Expected Output
| month(VARCHAR) | country(VARCHAR) | approved_count(INTEGER) | approved_amount(INTEGER) | chargeback_count(INTEGER) | chargeback_amount(INTEGER) |
|---|---|---|---|---|---|
| 2019-05 | US | 1 | 1000 | 1 | 2000 |
| 2019-06 | IN | 1 | 5000 | 0 | 0 |
| 2019-06 | US | 2 | 7000 | 1 | 1000 |
| 2019-07 | UK | 1 | 500 | 1 | 500 |
| 2019-09 | IN | 0 | 0 | 1 | 5000 |
Tags
MediumBasic SQLUNION ALLAggregationDate Manipulation
20-25 min
42%