AlgorithmJourney/LeetCode/sql/LeetCode1193.sql at main · HenryJi529/AlgorithmJourney · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
    DATE_FORMAT(trans_date, '%Y-%m') month,
    country,
    COUNT(*) trans_count,
    SUM(
        CASE
            when state = 'approved' then 1
            else 0
        end
    ) approved_count,
    SUM(amount) trans_total_amount,
    SUM(
        CASE
            when state = 'approved' then amount
            else 0
        end
    ) approved_total_amount
from Transactions
group by
    month,
    country;