LeetCode 1205 - Monthly Transactions II

This problem asks us to generate a monthly financial summary for each country using information from two database tables: Transactions and Chargebacks. The Transactions table stores incoming transactions.

leetcodemediumdatabase

LeetCode Problem 1205

Difficulty: 🟡 Medium
Topics: Database

Solution

LeetCode 1205 - Monthly Transactions II

Problem Understanding

This problem asks us to generate a monthly financial summary for each country using information from two database tables: Transactions and Chargebacks.

The Transactions table stores incoming transactions. Each transaction has a unique ID, a country, a state (approved or declined), an amount, and the transaction date.

The Chargebacks table stores chargeback events. Each chargeback references a transaction from the Transactions table through the trans_id foreign key. A chargeback also has its own date, which represents when the chargeback occurred.

The goal is to produce, for every (month, country) combination, the following aggregated values:

  • Number of approved transactions
  • Total amount of approved transactions
  • Number of chargebacks
  • Total amount of chargebacks

The month must be formatted as YYYY-MM.

An important detail is that chargebacks are grouped by the chargeback date, not the original transaction date. However, the country and amount information must still come from the original transaction in the Transactions table.

The note about ignoring rows with all zeros means that we should only return month-country combinations that contain at least one approved transaction or at least one chargeback.

The problem guarantees that every chargeback references a valid transaction. This means we can safely join Chargebacks with Transactions using the transaction ID.

Even though explicit constraints are not shown, database problems on LeetCode are generally designed for efficient set-based SQL solutions. A naive procedural approach that repeatedly scans tables would not scale well. Instead, aggregation and grouping operations should be pushed into SQL itself.

Several edge cases are important:

  • A month may contain approved transactions but no chargebacks.
  • A month may contain chargebacks but no approved transactions.
  • Transactions with state declined do not contribute to approved totals, but they can still appear in chargebacks.
  • Multiple chargebacks may exist across different months.
  • Some months may contain data for multiple countries.

The solution must correctly combine these cases into a single aggregated result.

Approaches

Brute Force Approach

A brute force strategy would iterate through every possible (month, country) pair and repeatedly scan the tables to compute the required aggregates.

For approved transactions, we would search the Transactions table for matching rows with state approved. For chargebacks, we would join each chargeback back to its original transaction and aggregate matching values manually.

This approach is logically correct because it explicitly computes every required metric. However, it is inefficient because it repeatedly scans large tables and performs redundant computations.

In procedural terms, this could become extremely expensive if the number of months, countries, transactions, and chargebacks grows large.

Optimal Approach

The key insight is that SQL databases are optimized for grouping and aggregation operations.

Instead of manually iterating through rows, we can:

  1. Aggregate approved transactions by (month, country)
  2. Aggregate chargebacks by (month, country)
  3. Combine the two result sets
  4. Perform a final grouping to merge overlapping rows

The cleanest way to achieve this is to use UNION ALL.

We create one query that produces approved transaction statistics and another query that produces chargeback statistics. Each query fills in the fields it does not use with zeros.

After combining the datasets with UNION ALL, we group again by (month, country) and sum the columns.

This approach is efficient because each table is scanned only once, and aggregation work is delegated to the database engine.

Approach Time Complexity Space Complexity Notes
Brute Force O(T × C) or worse O(1) Repeated scans over tables
Optimal O(T + C) O(T + C) Uses SQL aggregation and grouping efficiently

Here:

  • T = number of transaction rows
  • C = number of chargeback rows

Algorithm Walkthrough

  1. First, process approved transactions from the Transactions table.

For each transaction with state approved, extract:

  • The month using date formatting
  • The country
  • A count of approved transactions
  • The approved transaction amount

Since this query only handles approved transactions, set the chargeback fields to zero. 2. Next, process chargebacks.

Join the Chargebacks table with the Transactions table using:

Chargebacks.trans_id = Transactions.id

This join is necessary because the Chargebacks table does not contain country or amount information. 3. For each chargeback row:

  • Extract the chargeback month from Chargebacks.trans_date
  • Use the country from Transactions
  • Count the chargeback
  • Sum the original transaction amount

Since this query only handles chargebacks, set the approved transaction fields to zero. 4. Combine the two result sets using UNION ALL.

UNION ALL is preferred over UNION because we do not want duplicate elimination. We want all aggregated rows preserved. 5. Perform a final grouping by (month, country).

Sum all four metric columns:

  • approved_count
  • approved_amount
  • chargeback_count
  • chargeback_amount
  1. Return the final aggregated table.

Why it works

The algorithm works because every approved transaction contributes exactly once to the approved aggregates, and every chargeback contributes exactly once to the chargeback aggregates.

By converting both datasets into the same schema and combining them with UNION ALL, the final grouping step naturally merges rows belonging to the same (month, country) pair.

The aggregation is mathematically correct because summation is associative and independent across categories.

SQL Solution

# MySQL solution

SELECT
    month,
    country,
    SUM(approved_count) AS approved_count,
    SUM(approved_amount) AS approved_amount,
    SUM(chargeback_count) AS chargeback_count,
    SUM(chargeback_amount) AS chargeback_amount
FROM
(
    SELECT
        DATE_FORMAT(trans_date, '%Y-%m') AS month,
        country,
        COUNT(*) AS approved_count,
        SUM(amount) AS approved_amount,
        0 AS chargeback_count,
        0 AS chargeback_amount
    FROM Transactions
    WHERE state = 'approved'
    GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country

    UNION ALL

    SELECT
        DATE_FORMAT(c.trans_date, '%Y-%m') AS month,
        t.country,
        0 AS approved_count,
        0 AS approved_amount,
        COUNT(*) AS chargeback_count,
        SUM(t.amount) AS chargeback_amount
    FROM Chargebacks c
    JOIN Transactions t
        ON c.trans_id = t.id
    GROUP BY DATE_FORMAT(c.trans_date, '%Y-%m'), t.country
) AS combined
GROUP BY month, country;

The solution is divided into two major aggregation queries.

The first query handles approved transactions only. It filters rows using:

WHERE state = 'approved'

It then groups by month and country and computes the approved counts and amounts.

The second query handles chargebacks. Since the Chargebacks table does not contain country or amount information, we join it with Transactions.

The grouping for chargebacks is based on the chargeback date because the problem statement asks for chargeback statistics by the month the chargeback occurred.

The two result sets are combined using UNION ALL. Both queries return identical column structures, which allows them to be merged seamlessly.

Finally, the outer query groups again by (month, country) and sums the metric columns to produce the final result.

Worked Example

Example 1

Input Tables

Transactions

id country state amount trans_date
101 US approved 1000 2019-05-18
102 US declined 2000 2019-05-19
103 US approved 3000 2019-06-10
104 US declined 4000 2019-06-13
105 US approved 5000 2019-06-15

Chargebacks

trans_id trans_date
102 2019-05-29
101 2019-06-30
105 2019-09-18

Step 1, Approved Transaction Aggregation

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

Explanation:

  • Transaction 101 contributes to May
  • Transactions 103 and 105 contribute to June
  • Declined transactions are ignored

Step 2, Chargeback Aggregation

month country approved_count approved_amount chargeback_count chargeback_amount
2019-05 US 0 0 1 2000
2019-06 US 0 0 1 1000
2019-09 US 0 0 1 5000

Explanation:

  • Chargeback for transaction 102 occurs in May
  • Chargeback for transaction 101 occurs in June
  • Chargeback for transaction 105 occurs in September

Step 3, Final Aggregation

After combining and regrouping:

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

Complexity Analysis

Measure Complexity Explanation
Time O(T + C) Each table is scanned once
Space O(T + C) Intermediate grouped result sets

The database engine performs grouping and aggregation internally using optimized execution plans. The transaction table is scanned once for approved transactions, and the chargeback table is scanned once for chargeback aggregation.

The temporary grouped result sets require additional memory proportional to the number of distinct (month, country) combinations.

Test Cases

# Example 1
# Standard mixed case with approved transactions and chargebacks

# Expected:
# 2019-05 US -> approved(1,1000), chargeback(1,2000)
# 2019-06 US -> approved(2,8000), chargeback(1,1000)
# 2019-09 US -> approved(0,0), chargeback(1,5000)

# Only approved transactions
# Verifies rows appear even without chargebacks

# Transactions:
# approved: 100, 200
# Expected approved_count = 2
# Expected approved_amount = 300

# Only chargebacks
# Verifies months with zero approved transactions are still included

# Chargeback exists for declined transaction
# Expected approved_count = 0
# Expected chargeback_count = 1

# Multiple countries
# Ensures grouping separates countries correctly

# US and CA transactions should produce separate rows

# Multiple months
# Ensures monthly grouping works correctly

# January and February should appear separately

# Declined transactions without chargebacks
# Ensures they do not contribute to output

# Expected no row generated

# Multiple chargebacks in same month
# Verifies aggregation sums correctly

# Expected chargeback_count > 1
# Expected chargeback_amount summed correctly
Test Why
Example input Validates standard mixed aggregation
Only approved transactions Ensures approved-only rows appear
Only chargebacks Ensures chargeback-only rows appear
Multiple countries Verifies country grouping
Multiple months Verifies monthly grouping
Declined without chargeback Ensures invalid rows excluded
Multiple chargebacks Verifies aggregation correctness

Edge Cases

Chargebacks for Declined Transactions

A common mistake is assuming chargebacks only apply to approved transactions. The problem explicitly states that chargebacks may reference declined transactions as well.

The implementation handles this correctly because the chargeback aggregation joins directly with all transactions, without filtering on state.

Months with Only Chargebacks

Some months may contain chargebacks but no approved transactions. A naive inner join between approved transactions and chargebacks would accidentally exclude these rows.

The solution avoids this issue by aggregating approved transactions and chargebacks independently before combining them with UNION ALL.

Declined Transactions Without Chargebacks

Declined transactions should not contribute to approved statistics, and if they have no chargebacks, they should not appear in the result at all.

The implementation naturally handles this because:

  • The approved query filters them out
  • The chargeback query only includes transactions referenced by chargebacks

As a result, irrelevant rows never enter the combined dataset.