SQL Qs - Monthly Transaction II

LeetCode Q 1205 - Monthly Transaction II

Given tables Transactions and ChangeBacks, write a SQL solution to get the number of approved transactions and corresponding amounts, as well as the number of changeback transactions and corresponding amounts of each country in each month.

For example,

The result is

Solution:

SELECT 
month,
country,
SUM(type = 'approved') AS approved_count,
SUM(IF(type = 'approved', amount, 0)) AS approved_amount,
SUM(type = 'chargeback') AS changeback_count,
SUM(IF(type = 'chargeback', amount, 0)) AS changeback_amount
FROM (
        SELECT date_format(t.trans_date,'%Y-%m') AS month,
                t.country,
                t.amount,
                'approved' AS type
        FROM Transactions AS t
        WHERE state = 'approved' 
        UNION ALL
        SELECT date_format(c.trans_date,'%Y-%m') AS month,
               t.country,
               t.amount,
               'chargeback' AS type
        FROM Transactions AS t
        INNER JOIN Chargebacks AS c ON t.id = c.trans_id
    ) AS sub_query
GROUP BY month, country;

Tips:

1. MySQL UNION Operator

Syntax:

# UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

# UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Usage: The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order

More detailed explanation can be found here