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 | +---------+---------+----------------+-----------------+------------------+-------------------+
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.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subqueries per Metric | O(n^2) | O(1) | Simple logic but inefficient; useful only for small datasets or quick prototypes |
| JOIN with Conditional Aggregation | O(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 |
Leetcode MEDIUM 1205 - FULL JOINs in SQL - Monthly Transactions 2 | Everyday Data Science • Everyday Data Science • 876 views views
Watch 1 more video solutions →Practice Monthly Transactions II with our built-in code editor and test cases.
Practice on FleetCode