Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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              |
+---------+---------+----------------+-----------------+------------------+-------------------+

Solution

Method 1 - SQL Aggregation and Join

Intuition

We need to aggregate approved transactions and chargebacks by month and country. For chargebacks, join with Transactions to get country and amount. For approved transactions, group by month and country. Then, combine both using UNION or FULL OUTER JOIN, and sum up counts and amounts.

Approach

  1. For approved transactions, group by month and country, count and sum amount.
  2. For chargebacks, join Chargebacks with Transactions, group by month and country, count and sum amount.
  3. Combine both using UNION ALL, then group again to sum up counts and amounts for each month and country.
  4. Ignore rows where all values are zero.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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 month, 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 month, t.country
) x
WHERE approved_count > 0 OR approved_amount > 0 OR chargeback_count > 0 OR chargeback_amount > 0
GROUP BY month, country

Complexity

  • ⏰ Time complexity: O(N) — Each row is processed once per aggregation.
  • 🧺 Space complexity: O(M) — For result groups, M = number of unique (month, country) pairs.