Monthly Country Transaction Health Summary

Medium

A payments platform stores incoming transaction events in one relational table: Transactions(id, country, state, amount, trans_date) Column details: id: unique transaction identifier country: market/country label for the transaction state: processing outcome, either "approved" or "declined" amount: transaction amount trans_date: date of the event (YYYY-MM-DD) Create a monthly reporting view for each (month, country) pair that appears in the data. For every month and country, compute: trans_count: total number of transactions approved_count: number of approved transactions trans_total_amount: total amount across all transactions approved_total_amount: total amount across approved transactions only Output requirements: month must be in YYYY-MM format return exactly these columns: month, country, trans_count, approved_count, trans_total_amount, approved_total_amount results may be returned in any order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

transactions:
| id | country | state    | amount | trans_date  |
|----|---------|----------|--------|-------------|
| 1  | US      | approved | 700    | 2024-01-05  |
| 2  | US      | declined | 300    | 2024-01-11  |
| 3  | US      | approved | 500    | 2024-02-02  |
| 4  | FR      | approved | 1000   | 2024-02-19  |
| 5  | FR      | declined | 120    | 2024-02-22  |

Output

[
  {"month":"2024-01","country":"US","trans_count":2,"approved_count":1,"trans_total_amount":1000,"approved_total_amount":700},
  {"month":"2024-02","country":"FR","trans_count":2,"approved_count":1,"trans_total_amount":1120,"approved_total_amount":1000},
  {"month":"2024-02","country":"US","trans_count":1,"approved_count":1,"trans_total_amount":500,"approved_total_amount":500}
]

Explanation

Rows are grouped by month and country. Approved metrics are computed conditionally while total metrics include all rows.

Example 2

Input

transactions:
| id | country | state    | amount | trans_date  |
|----|---------|----------|--------|-------------|
| 10 | IN      | declined | 200    | 2025-03-01  |
| 11 | IN      | declined | 50     | 2025-03-07  |
| 12 | BR      | declined | 30     | 2025-03-20  |

Output

[
  {"month":"2025-03","country":"BR","trans_count":1,"approved_count":0,"trans_total_amount":30,"approved_total_amount":0},
  {"month":"2025-03","country":"IN","trans_count":2,"approved_count":0,"trans_total_amount":250,"approved_total_amount":0}
]

Explanation

Declined rows still contribute to trans_count and trans_total_amount. Approved metrics remain zero when no approved rows exist.

Example 3

Input

transactions:
| id | country | state    | amount     | trans_date  |
|----|---------|----------|------------|-------------|
| 20 | JP      | approved | 0          | 2024-12-31  |
| 21 | JP      | approved | 1000000000 | 2024-12-15  |
| 22 | JP      | declined | 50         | 2024-12-19  |
| 23 | CA      | approved | 75         | 2024-12-03  |

Output

[
  {"month":"2024-12","country":"CA","trans_count":1,"approved_count":1,"trans_total_amount":75,"approved_total_amount":75},
  {"month":"2024-12","country":"JP","trans_count":3,"approved_count":2,"trans_total_amount":1000000050,"approved_total_amount":1000000000}
]

Explanation

The query must correctly handle zero and very large amounts while preserving per-country grouping within the same month.

Constraints

1 <= number of rows in transactions <= 300000
1 <= id <= 1000000000, and id values are unique
country is a non-empty string
state is one of: approved, declined
0 <= amount <= 1000000000
trans_date is a valid date in YYYY-MM-DD format
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact columns and names required by the problem

transactions: | id | country | state | amount | trans_date | |----|---------|----------|--------|-------------| | 1 | US | approved | 700 | 2024-01-05 | | 2 | US | declined | 300 | 2024-01-11 | | 3 | US | approved | 500 | 2024-02-02 | | 4 | FR | approved | 1000 | 2024-02-19 | | 5 | FR | declined | 120 | 2024-02-22 |

[ {"month":"2024-01","country":"US","trans_count":2,"approved_count":1,"trans_total_amount":1000,"approved_total_amount":700}, {"month":"2024-02","country":"FR","trans_count":2,"approved_count":1,"trans_total_amount":1120,"approved_total_amount":1000}, {"month":"2024-02","country":"US","trans_count":1,"approved_count":1,"trans_total_amount":500,"approved_total_amount":500} ]

transactions: | id | country | state | amount | trans_date | |----|---------|----------|--------|-------------| | 10 | IN | declined | 200 | 2025-03-01 | | 11 | IN | declined | 50 | 2025-03-07 | | 12 | BR | declined | 30 | 2025-03-20 |

[ {"month":"2025-03","country":"BR","trans_count":1,"approved_count":0,"trans_total_amount":30,"approved_total_amount":0}, {"month":"2025-03","country":"IN","trans_count":2,"approved_count":0,"trans_total_amount":250,"approved_total_amount":0} ]

transactions: | id | country | state | amount | trans_date | |----|---------|----------|------------|-------------| | 20 | JP | approved | 0 | 2024-12-31 | | 21 | JP | approved | 1000000000 | 2024-12-15 | | 22 | JP | declined | 50 | 2024-12-19 | | 23 | CA | approved | 75 | 2024-12-03 |

[ {"month":"2024-12","country":"CA","trans_count":1,"approved_count":1,"trans_total_amount":75,"approved_total_amount":75}, {"month":"2024-12","country":"JP","trans_count":3,"approved_count":2,"trans_total_amount":1000000050,"approved_total_amount":1000000000} ]