Skip to main content
Talentd

Monthly Transactions II - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Wish
Practice this problem

Problem Statement

Table: Transactions

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| country        | varchar |
| state          | enum    |
| amount         | int     |
| trans_date     | date    |
+----------------+---------+
id is the column of unique values of this table.
The table has information about incoming transactions.
The state column is an ENUM (category) of type ["approved", "declined"].

Table: Chargebacks

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| trans_date     | date    |
+----------------+---------+
Chargebacks contains basic information regarding incoming chargebacks from some transactions placed in Transactions table.
trans_id is a foreign key (reference column) to the id column of Transactions table.
Each chargeback corresponds to a transaction made previously even if they were not approved.

 

Write a solution to find for each month and country: the number of approved transactions and their total amount, the number of chargebacks, and their total amount.

Note: In your solution, given the month and country, ignore rows with all zeros.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Transactions table:
+-----+---------+----------+--------+------------+
| 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 table:
+----------+------------+
| trans_id | trans_date |
+----------+------------+
| 102      | 2019-05-29 |
| 101      | 2019-06-30 |
| 105      | 2019-09-18 |
+----------+------------+
Output: 
+---------+---------+----------------+-----------------+------------------+-------------------+
| 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              |
+---------+---------+----------------+-----------------+------------------+-------------------+

Approach Overview

Problem Overview: You need to generate a monthly financial report by country. The report must combine data from transactions and chargebacks, counting how many transactions occurred, how many were approved, and how many were later charged back, along with their total amounts.

Approach 1: Aggregation with JOIN and GROUP BY (O(n) time, O(1) extra space)

The key idea is to aggregate monthly transaction metrics and chargeback metrics separately, then combine them into a single report. Start by extracting the month from trans_date using DATE_FORMAT(trans_date, '%Y-%m'). Group transactions by month and country, and compute metrics using conditional aggregation such as COUNT(*), SUM(amount), and SUM(IF(state = 'approved', amount, 0)). This gives transaction counts and totals for each month-country pair.

Chargebacks reference the original transaction through trans_id. Join the Chargebacks table with Transactions to retrieve the country and amount of the original transaction. Then aggregate chargebacks by the same month and country fields using COUNT(*) and SUM(amount). This produces the monthly chargeback statistics.

Because both datasets represent different event types, combine them using UNION ALL into a single derived table. For transaction rows, populate chargeback columns with zeros; for chargeback rows, populate transaction columns with zeros. After merging the rows, perform a final GROUP BY month, country to sum all metrics together. The result contains complete monthly summaries with transaction counts, approved counts, totals, and chargeback statistics.

This pattern is common in SQL reporting problems: normalize event types into a unified dataset and aggregate once. It avoids complicated joins with conditional logic and keeps the query readable.

Recommended for interviews: Interviewers expect a clean aggregation strategy using GROUP BY, conditional sums, and a join between chargebacks and transactions. A naive approach using multiple correlated subqueries works but scans the same data repeatedly. The UNION ALL aggregation approach demonstrates strong understanding of database reporting patterns and efficient SQL aggregation. Time complexity is O(n) because each table is scanned once, which is optimal for this type of reporting query.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Correlated Subqueries per MetricO(n^2)O(1)Simple logic but inefficient; useful only for small datasets or quick prototypes
JOIN with Conditional AggregationO(n)O(1)When combining related tables and computing multiple metrics in one grouped query
UNION ALL Event Aggregation (Optimal)O(n)O(1)Best for reporting queries that merge different event types like transactions and chargebacks

Video Solution

Leetcode MEDIUM 1205 - FULL JOINs in SQL - Monthly Transactions 2 | Everyday Data Science • Everyday Data Science • 914 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Monthly Transactions II easy or hard?
Monthly Transactions II is rated Medium difficulty. The challenge is understanding how to combine transaction and chargeback data correctly while producing aggregated monthly metrics. Strong familiarity with SQL joins and conditional aggregation makes the problem straightforward.
Monthly Transactions II Python/Java solution
This problem is typically solved using SQL rather than Python or Java because it operates directly on relational tables. In practice environments like MySQL, the solution uses GROUP BY, joins between Transactions and Chargebacks, and aggregation functions to compute monthly statistics.
How to solve Monthly Transactions II in O(n)?
Extract the month from transaction dates, aggregate transaction metrics by month and country, and separately aggregate chargebacks after joining them with transactions. Use UNION ALL to combine both result sets and run a final GROUP BY to compute totals. This ensures each table is scanned only once.
What is the best approach for Monthly Transactions II?
The most efficient approach aggregates transactions and chargebacks separately, merges them using UNION ALL, and performs a final GROUP BY on month and country. This method scans each table once and calculates all metrics with conditional aggregation. It keeps the SQL readable and runs in O(n) time.
Is Monthly Transactions II asked at Google/Amazon/Meta?
Database reporting and SQL aggregation problems like Monthly Transactions II commonly appear in interviews at companies such as Amazon, Google, and Meta. Variations often test GROUP BY, joins between related tables, and conditional aggregation in SQL.
What data structure is used in Monthly Transactions II?
The problem relies on relational database tables and SQL aggregation rather than traditional in-memory data structures. The key operations are GROUP BY, JOIN, conditional SUM/COUNT, and UNION ALL to combine event datasets.
What is the time complexity of Monthly Transactions II?
The optimal SQL solution runs in O(n) time where n is the number of rows in the Transactions and Chargebacks tables. Each table is scanned once for aggregation and the final grouping step processes the combined rows. Space complexity is O(1) aside from the result set.

Ready to solve this problem?

Practice Monthly Transactions II with our built-in code editor and test cases.

Practice on FleetCode
Previous

Monthly Transactions I