30 Days Of LeetCode Database Day 13 - 1193. Monthly Transactions I

1**Example** 1:
2
3Input: 
4Transactions table:
5+------+---------+----------+--------+------------+
6| id   | country | state    | amount | trans_date |
7+------+---------+----------+--------+------------+
8| 121  | US      | approved | 1000   | 2018-12-18 |
9| 122  | US      | declined | 2000   | 2018-12-19 |
10| 123  | US      | approved | 2000   | 2019-01-01 |
11| 124  | DE      | approved | 2000   | 2019-01-07 |
12+------+---------+----------+--------+------------+
13Output: 
14+----------+---------+-------------+----------------+--------------------+-----------------------+
15| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
16+----------+---------+-------------+----------------+--------------------+-----------------------+
17| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
18| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
19| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
20+----------+---------+-------------+----------------+--------------------+-----------------------+
21 

1SELECT 
2DATE_FORMAT(trans_date, '%Y-%m') AS month,
3country, 
4COUNT(id) AS  trans_count,
5SUM(state = 'approved') AS approved_count,
6SUM(amount) AS trans_total_amount,
7SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
8FROM Transactions GROUP BY month, country;