Monthly Country Approval and Chargeback Reconciliation

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

chargebacks =

[{"trans_id":202,"trans_date":"2024-01-28"},{"trans_id":201,"trans_date":"2024-02-05"},{"trans_id":205,"trans_date":"2024-03-12"}]

transactions =

[{"id":201,"country":"US","state":"approved","amount":700,"trans_date":"2024-01-03"},{"id":202,"country":"US","state":"declined","amount":500,"trans_date":"2024-01-07"},{"id":203,"country":"US","state":"approved","amount":900,"trans_date":"2024-02-02"},{"id":204,"country":"US","state":"approved","amount":600,"trans_date":"2024-02-20"},{"id":205,"country":"US","state":"declined","amount":300,"trans_date":"2024-02-24"}]

Monthly Country Approval and Chargeback Reconciliation

Hints

Monthly Country Approval and Chargeback Reconciliation

Hints