00:00:00

Description

Editorial

Monthly Country Transaction Health Summary

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

transactions =

[{"id":121,"country":"US","state":"approved","amount":1000,"trans_date":"2018-12-18"},{"id":122,"country":"US","state":"declined","amount":2000,"trans_date":"2018-12-19"},{"id":123,"country":"US","state":"approved","amount":2000,"trans_date":"2019-01-01"},{"id":124,"country":"DE","state":"approved","amount":2000,"trans_date":"2019-01-07"}]

Loading problem...

101

00:00:00

Description

Editorial

Monthly Country Transaction Health Summary

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

transactions =

Monthly Country Transaction Health Summary

Hints

Monthly Country Transaction Health Summary

Hints