Description

Table: Transactions

Column NameType
idint
countryvarchar
stateenum
amountint
trans_datedate
  • 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 NameType
trans_idint
trans_datedate
  • 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.

Problem Statement

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:
idcountrystateamounttrans_date
101USapproved10002019-05-18
102USdeclined20002019-05-19
103USapproved30002019-06-10
104USdeclined40002019-06-13
105USapproved50002019-06-15
  • Chargebacks table:
trans_idtrans_date
1022019-05-29
1012019-06-30
1052019-09-18

Output:

monthcountryapproved_countapproved_amountchargeback_countchargeback_amount
2019-05US1100012000
2019-06US2800011000
2019-09US0015000

Solution

Let’s understand the problem first. You need to find approved_count, approved_amount which you can find by grouping by month and country and calculating count of rows where state = "approved" and sum of amount for those rows. Next, in order to find chargeback_count and chargeback_amount, you need to find similar aggregation. However, because the country information is in the Transactions table, you first need to join with Transactions table. This way you can calculate all aggregations in single query. The join query looks like this.

1SELECT t.id, DATE_FORMAT('%Y-%m', t.trans_date) AS month, t.country, t.state, t.amount, c.trans_date
2    FROM Transactions t
3    LEFT JOIN Chargebacks c
4    ON t.id = c.trans_id;

One more thing to notice is that you need to consider trans_date from Chargebacks column when calculating chargeback aggregations. This is different from original trans_date from Transactions table.

This means you need a subquery to calculate approved transactions and separate subquery for calculating chargebacks.

1. Approved Transactions

This is relatively straight forward.

1WITH approved_trans AS (
2    SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country,
3        COUNT(1) AS approved_count,
4        SUM(amount) AS approved_amount
5        FROM Transactions
6        WHERE state = 'approved'
7        GROUP BY month, country
8) SELECT * FROM approved_trans;
monthcountryapproved_countapproved_amount
2019-05US11000
2019-06US28000

2. Chargebacks

For Chargebacks, you need information from both tables. So, you first join and then calculate the aggregations.

1WITH chargeback_trans AS (
2    SELECT DATE_FORMAT(c.trans_date, '%Y-%m') AS month, t.country,
3        COUNT(1) AS chargeback_count,
4        SUM(t.amount) AS chargeback_amount
5        FROM Transactions t
6        JOIN Chargebacks c
7        ON t.id = c.trans_id
8        GROUP BY month, country
9) SELECT * FROM chargeback_trans;
monthcountrychargeback_countchargeback_amount
2019-06US11000
2019-05US12000
2019-09US15000

3. Final Result

Once you have approved_trans and chargeback_trans, all you need is to combine both of them into one table. You can do this by using either JOIN or UNION operations. Let me show both.

1. Using JOIN

If you join approved_trans and chargeback_trans tables by month and country, you will get the final result. You need to perform FULL OUTER JOIN to retrieve all rows from both tables and wherever there are NULL, you need to replace it with 0.

1SELECT COALESCE(t.month, c.month) AS month, COALESCE(t.country, c.country) AS country,
2    IFNULL(t.approved_count, 0) AS approved_count,
3    IFNULL(t.approved_amount, 0) AS approved_amount,
4    IFNULL(c.chargeback_count, 0) AS chargeback_count,
5    IFNULL(c.chargeback_amount, 0) AS chargeback_amount
6    FROM approved_trans t
7    FULL OUTER JOIN chargeback_trans c
8    ON t.month = c.month AND t.country = c.country;
monthcountryapproved_countapproved_amountchargeback_countchargeback_amount
2019-05US1100012000
2019-06US2800011000
2019-09US0015000

Note: MySQL doesn’t have FULL OUTER JOIN so you need to use LEFT JOIN and RIGHT JOIN instead.

2. Using UNION

In this approach, you combine the rows from approved_trans and chargeback_trans tables using UNION operation.

1WITH unioned AS (
2    SELECT month, country, approved_count, approved_amount, 0 AS chargeback_count, 0 AS chargeback_amount
3        FROM approved_trans
4        UNION ALL
5        SELECT month, country, 0 AS approved_count, 0 AS approved_amount, chargeback_count, chargeback_amount
6        FROM chargebacks_trans
7) SELECT * FROM unioned;
monthcountryapproved_countapproved_amountchargeback_countchargeback_amount
2019-05US1100000
2019-06US2800000
2019-06US0011000
2019-05US0012000
2019-09US0015000

Once you have above set of results, all you need is to perform aggregation on month and country to get the final results.

1SELECt month, country,
2    SUM(approved_count) AS approved_count,
3    SUM(approved_amount) AS approved_amount,
4    SUM(chargeback_count) AS chargeback_count,
5    SUM(chargeback_amount) AS chargeback_amount
6    FROM unioned
7    GROUP BY 1, 2;

The final solution looks like this.

 1WITH approved_trans AS (
 2    SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country,
 3        COUNT(1) AS approved_count,
 4        SUM(amount) AS approved_amount
 5        FROM Transactions
 6        WHERE state = 'approved'
 7        GROUP BY month, country
 8), chargeback_trans AS (
 9    SELECT DATE_FORMAT(c.trans_date, '%Y-%m') AS month, t.country,
10        COUNT(1) AS chargeback_count,
11        SUM(t.amount) AS chargeback_amount
12        FROM Transactions t
13        JOIN Chargebacks c
14        ON t.id = c.trans_id
15        GROUP BY month, country
16), unioned AS (
17    SELECT month, country, approved_count, approved_amount, 0 AS chargeback_count, 0 AS chargeback_amount
18        FROM approved_trans
19        UNION ALL
20        SELECT month, country, 0 AS approved_count, 0 AS approved_amount, chargeback_count, chargeback_amount
21        FROM chargeback_trans
22) SELECt month, country,
23    SUM(approved_count) AS approved_count,
24    SUM(approved_amount) AS approved_amount,
25    SUM(chargeback_count) AS chargeback_count,
26    SUM(chargeback_amount) AS chargeback_amount
27    FROM unioned
28    GROUP BY month, country;