https://www.youtube.com/watch?v=NcT4TeFT7Vc

create table transactions (
id int primary key,
country varchar(15),
state varchar(15),
amount int,
trans_date date
)

insert into transactions values(1,'US','approved',1000,'2023-12-18')
insert into transactions values(2,'US','declined',2000,'2023-12-19')
insert into transactions values(3,'US','approved',2000,'2024-01-01')
insert into transactions values(4,'India','approved',2000,'2023-01-07')

CONCAT()

-- Preview original data with month and country columns
SELECT 
    *, 
    CONCAT(YEAR(trans_date), '-', MONTH(trans_date)) AS month,
    country 
FROM 
    tees;

-- Aggregate total transactions by month and country
WITH total AS (
    SELECT 
        CONCAT(YEAR(trans_date), '-', MONTH(trans_date)) AS month,
        country,
        COUNT(*) AS total_trans_count,
        SUM(amount) AS trans_amt
    FROM 
        tees
    GROUP BY 
        country, MONTH(trans_date)
),

-- Aggregate approved transactions by month and country
approved AS (
    SELECT 
        CONCAT(YEAR(trans_date), '-', MONTH(trans_date)) AS month,
        country,
        COUNT(*) AS approved_count,
        SUM(amount) AS approved_amt
    FROM 
        tees
    WHERE 
        state = 'approved'
    GROUP BY 
        country, MONTH(trans_date)
)

-- Final result combining total and approved transaction metrics
SELECT 
    t.month,
    t.country,
    t.total_trans_count,
    t.trans_amt,
    a.approved_count,
    a.approved_amt
FROM 
    total t
LEFT JOIN 
    approved a 
    ON t.month = a.month AND t.country = a.country;