Monthly Transactions II - Practice Coding Problems

Monthly Transactions II - Problem

Database Medium

You have two tables: Transactions and Chargebacks.

The Transactions table contains information about incoming transactions with columns for id, country, state (approved/declined), amount, and trans_date.

The Chargebacks table contains information about chargebacks with trans_id (foreign key to Transactions.id) and trans_date (chargeback date).

Task: For each month and country, find:

Number of approved transactions and their total amount
Number of chargebacks and their total amount

Note: Ignore rows with all zeros. Chargebacks can occur for any transaction (approved or declined).

Table Schema

Transactions

Column Name	Type	Description
`id` PK	int	Primary key, unique transaction ID
`country`	varchar	Country where transaction occurred
`state`	enum	Transaction status: 'approved' or 'declined'
`amount`	int	Transaction amount
`trans_date`	date	Transaction date

Primary Key: id

Chargebacks

Column Name	Type	Description
`trans_id` PK	int	Foreign key referencing Transactions.id
`trans_date`	date	Chargeback date

Primary Key: trans_id

Input & Output

Example 1 — Basic Transactions and Chargebacks

Input Tables:

Transactions

id	country	state	amount	trans_date
1	US	approved	1000	2019-05-18
2	US	declined	2000	2019-06-17
3	US	approved	3000	2019-06-17
4	US	approved	4000	2019-06-17

Chargebacks

trans_id	trans_date
2	2019-06-15
3	2019-06-15

Output:

month	country	approved_count	approved_amount	chargeback_count	chargeback_amount
2019-05	US	1	1000	0	0
2019-06	US	2	7000	2	5000

💡 Note:

For 2019-05: 1 approved transaction (id=1) worth $1000, no chargebacks. For 2019-06: 2 approved transactions (id=3,4) worth $7000 total, and 2 chargebacks (for transactions 2 and 3) worth $5000 total. Note that transaction 2 was declined but still had a chargeback.

Example 2 — Multiple Countries

Input Tables:

Transactions

id	country	state	amount	trans_date
1	US	approved	1000	2019-05-18
2	CA	approved	2000	2019-05-18
3	CA	declined	1500	2019-05-19

Chargebacks

trans_id	trans_date
3	2019-06-01

Output:

month	country	approved_count	approved_amount	chargeback_count	chargeback_amount
2019-05	CA	1	2000	0	0
2019-05	US	1	1000	0	0
2019-06	CA	0	0	1	1500

💡 Note:

Shows data for multiple countries. In May, US had 1 approved transaction ($1000) and CA had 1 approved transaction ($2000). In June, CA had a chargeback for the declined transaction 3 ($1500).

Constraints

1 ≤ id ≤ 1000
country is a valid country code
state is either 'approved' or 'declined'
amount > 0
trans_date is a valid date

Visualization

Tap to expand

Understanding the Visualization

Input Tables

Transactions and Chargebacks tables

JOIN & GROUP

Combine tables and group by month/country

Aggregate

Count and sum approved transactions and chargebacks

Key Takeaway

🎯 Key Insight: Use UNION ALL to combine transaction types, then aggregate by time period and location

Asked in

a Amazon 23 M Microsoft 18 G Google 15

Use UNION ALL to combine approved transactions and chargebacks data, then group by month and country. Apply conditional aggregation to calculate separate metrics for transactions vs chargebacks.

Table Schema

Transactions

Column Name	Type	Description
`id` PK	int	Primary key, unique transaction ID
`country`	varchar	Country where transaction occurred
`state`	enum	Transaction status: 'approved' or 'declined'
`amount`	int	Transaction amount
`trans_date`	date	Transaction date

Primary Key: id

Chargebacks

Column Name	Type	Description
`trans_id` PK	int	Foreign key referencing Transactions.id
`trans_date`	date	Chargeback date

Primary Key: trans_id

Common Approaches

Approach	Time	Space	Notes
✓ LEFT JOIN with Date Aggregation	O(n + m)	O(k)	Join transactions with chargebacks, group by month/country, and aggregate separately

LEFT JOIN with Date Aggregation — Algorithm Steps

Step 1: LEFT JOIN Transactions with Chargebacks to get all transaction data
Step 2: Extract year-month from dates and group by month/country
Step 3: Use conditional aggregation to separate approved transactions from chargebacks
Step 4: Filter out rows where all metrics are zero

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

LEFT JOIN Transactions with Chargebacks

Group Data

Group by month and country

Aggregate

Count and sum by transaction type

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Linear scan of transactions (n) and chargebacks (m) tables

✓ Linear Growth

Space Complexity

O(k)

Space for k distinct month-country combinations

✓ Linear Space

34.5K Views

Medium Frequency

~20 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

LEFT JOIN with Date Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler