Monthly Transactions I

Task: Find for each month and country, the number of transactions, total amount, number of approved transactions and total approved amount.

Table: Transactions

+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| id          | INT         |
| country     | varchar(4)  |
| state       | varchar(10) |
| amount      | INT         |
| trans_date  | date        |
+-------------+-------------+

Input: Transactions table:

+-----+---------+----------+--------+------------+
| id  | country | state    | amount | trans_date |
+-----+---------+----------+--------+------------+
| 121 | US      | approved | 1000   | 2018-12-18 |
| 122 | US      | declined | 2000   | 2018-12-19 |
| 123 | US      | approved | 2000   | 2019-01-01 |
| 124 | DE      | approved | 2000   | 2019-01-07 |
+-----+---------+----------+--------+------------+

Output:

+---------+---------+-------------+----------------+--------------------+-----------------------+
| month   | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+---------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12 | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01 | DE      | 1           | 1              | 2000               | 2000                  |
| 2019-01 | US      | 1           | 1              | 2000               | 2000                  |
+---------+---------+-------------+----------------+--------------------+-----------------------+