Monthly Country Approval and Chargeback Reconciliation

Medium

A payments operations team maintains two event streams: transactions(id, country, state, amount, trans_date) chargebacks(trans_id, trans_date) Interpretation rules: transactions records inbound payment attempts. state is either "approved" or "declined". chargebacks contains dispute reversals and references transactions.id via trans_id. A chargeback may point to a transaction regardless of the original state. Build a reconciliation report at the (month, country) level with these metrics: approved_count: number of approved transactions approved_amount: total amount from approved transactions chargeback_count: number of chargeback rows chargeback_amount: total amount of transactions referenced by chargebacks Grouping logic: For approved metrics, month comes from transactions.trans_date. For chargeback metrics, month comes from chargebacks.trans_date. Country always comes from transactions.country. Output requirements: Return exactly these columns: month, country, approved_count, approved_amount, chargeback_count, chargeback_amount month format must be YYYY-MM Exclude rows where all four metrics are zero. Result order is not required. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

transactions:
| id  | country | state    | amount | trans_date  |
|-----|---------|----------|--------|-------------|
| 201 | US      | approved | 700    | 2024-01-03  |
| 202 | US      | declined | 500    | 2024-01-07  |
| 203 | US      | approved | 900    | 2024-02-02  |
| 204 | US      | approved | 600    | 2024-02-20  |
| 205 | US      | declined | 300    | 2024-02-24  |

chargebacks:
| trans_id | trans_date  |
|----------|-------------|
| 202      | 2024-01-28  |
| 201      | 2024-02-05  |
| 205      | 2024-03-12  |

Output

[
  {"month":"2024-01","country":"US","approved_count":1,"approved_amount":700,"chargeback_count":1,"chargeback_amount":500},
  {"month":"2024-02","country":"US","approved_count":2,"approved_amount":1500,"chargeback_count":1,"chargeback_amount":700},
  {"month":"2024-03","country":"US","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":300}
]

Explanation

Approved metrics use transaction month, while chargeback metrics use dispute month. A declined transaction can still contribute to chargeback totals.

Example 2

Input

transactions:
| id | country | state    | amount | trans_date  |
|----|---------|----------|--------|-------------|
| 1  | DE      | approved | 120    | 2025-03-02  |
| 2  | DE      | approved | 80     | 2025-03-15  |
| 3  | FR      | declined | 200    | 2025-03-20  |
| 4  | FR      | approved | 150    | 2025-04-01  |
| 5  | FR      | declined | 50     | 2025-04-05  |

chargebacks:
| trans_id | trans_date  |
|----------|-------------|
| 3        | 2025-04-17  |
| 2        | 2025-04-30  |
| 5        | 2025-05-04  |

Output

[
  {"month":"2025-03","country":"DE","approved_count":2,"approved_amount":200,"chargeback_count":0,"chargeback_amount":0},
  {"month":"2025-04","country":"DE","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":80},
  {"month":"2025-04","country":"FR","approved_count":1,"approved_amount":150,"chargeback_count":1,"chargeback_amount":200},
  {"month":"2025-05","country":"FR","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":50}
]

Explanation

The report can contain multiple countries in the same month. Each metric is aggregated independently and then reconciled per month-country key.

Example 3

Input

transactions:
| id | country | state    | amount | trans_date  |
|----|---------|----------|--------|-------------|
| 11 | IN      | declined | 300    | 2023-07-01  |
| 12 | IN      | declined | 700    | 2023-07-03  |

chargebacks:
| trans_id | trans_date  |
|----------|-------------|
| 11       | 2023-07-15  |
| 12       | 2023-08-01  |

Output

[
  {"month":"2023-07","country":"IN","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":300},
  {"month":"2023-08","country":"IN","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":700}
]

Explanation

Even when a country-month has no approved rows, it must still appear if chargeback activity exists.

Constraints

1 <= number of rows in transactions <= 300000
0 <= number of rows in chargebacks <= 300000
1 <= id <= 1000000000 and id values are unique in transactions
country is a non-empty string
state is one of: approved, declined
0 <= amount <= 1000000000
trans_date values are valid dates in YYYY-MM-DD format
Every chargebacks.trans_id references an existing transactions.id
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact required columns

transactions: | id | country | state | amount | trans_date | |-----|---------|----------|--------|-------------| | 201 | US | approved | 700 | 2024-01-03 | | 202 | US | declined | 500 | 2024-01-07 | | 203 | US | approved | 900 | 2024-02-02 | | 204 | US | approved | 600 | 2024-02-20 | | 205 | US | declined | 300 | 2024-02-24 | chargebacks: | trans_id | trans_date | |----------|-------------| | 202 | 2024-01-28 | | 201 | 2024-02-05 | | 205 | 2024-03-12 |

[ {"month":"2024-01","country":"US","approved_count":1,"approved_amount":700,"chargeback_count":1,"chargeback_amount":500}, {"month":"2024-02","country":"US","approved_count":2,"approved_amount":1500,"chargeback_count":1,"chargeback_amount":700}, {"month":"2024-03","country":"US","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":300} ]

transactions: | id | country | state | amount | trans_date | |----|---------|----------|--------|-------------| | 1 | DE | approved | 120 | 2025-03-02 | | 2 | DE | approved | 80 | 2025-03-15 | | 3 | FR | declined | 200 | 2025-03-20 | | 4 | FR | approved | 150 | 2025-04-01 | | 5 | FR | declined | 50 | 2025-04-05 | chargebacks: | trans_id | trans_date | |----------|-------------| | 3 | 2025-04-17 | | 2 | 2025-04-30 | | 5 | 2025-05-04 |

[ {"month":"2025-03","country":"DE","approved_count":2,"approved_amount":200,"chargeback_count":0,"chargeback_amount":0}, {"month":"2025-04","country":"DE","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":80}, {"month":"2025-04","country":"FR","approved_count":1,"approved_amount":150,"chargeback_count":1,"chargeback_amount":200}, {"month":"2025-05","country":"FR","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":50} ]

transactions: | id | country | state | amount | trans_date | |----|---------|----------|--------|-------------| | 11 | IN | declined | 300 | 2023-07-01 | | 12 | IN | declined | 700 | 2023-07-03 | chargebacks: | trans_id | trans_date | |----------|-------------| | 11 | 2023-07-15 | | 12 | 2023-08-01 |

[ {"month":"2023-07","country":"IN","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":300}, {"month":"2023-08","country":"IN","approved_count":0,"approved_amount":0,"chargeback_count":1,"chargeback_amount":700} ]