text: -- #Table: Transactions
  --  #
  -- #
  -- #+---------------+---------+
  -- #| Column Name   | Type    |
  -- #+---------------+---------+
  -- #| id            | int     |
  -- #| country       | varchar |
  -- #| state         | enum    |
  -- #| amount        | int     |
  -- #| trans_date    | date    |
  -- #+---------------+---------+
  -- #id is the primary key of this table.
  -- #The table has information about incoming transactions.
  -- #The state column is an enum of type ["approved", "declined"].
  -- #
  -- #
  -- #
  -- #
  -- # Write an SQL query to find for each month and country, the number of transacti
  -- #ons and their total amount, the number of approved transactions and their total
  -- #amount.
  -- #
  -- # The query result format is in the following example:
  -- #
  -- #
  -- #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 |
  -- #+------+---------+----------+--------+------------+
  -- #
  -- #Result table:
  --         #+----------+---------+-------------+----------------+--------------------+-----
  -- #------------------+
  -- #| month    | country | trans_count | approved_count | trans_total_amount | appr
  -- #oved_total_amount |
  -- #+----------+---------+-------------+----------------+--------------------+-----
  -- #------------------+
  -- #| 2018-12  | US      | 2           | 1              | 3000               | 1000
  -- #                  |
  -- #| 2019-01  | US      | 1           | 1              | 2000               | 2000
  -- #                  |
  -- #| 2019-01  | DE      | 1           | 1              | 2000               | 2000
  -- #                  |
  -- #+----------+---------+-------------+----------------+--------------------+-----
  -- #------------------+
  -- #
  -- # 👍 60 👎 13
  /*
    Runtime: 495 ms, faster than 84.93% of MySQL online submissions for Monthly Transactions I.
    Memory Usage: 0B, less than 100.00% of MySQL online submissions for Monthly Transactions I.
  */
  -- #leetcode submit region begin(Prohibit modification and deletion)
  -- # Write your MySQL query statement below
  SELECT DATE_FORMAT(trans_date, '%Y-%m')       AS month,
         country,
         COUNT(*)                               AS trans_count,
         SUM(state = 'approved')                AS approved_count,
         SUM(amount)                            AS trans_total_amount,
         SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
  FROM transactions
  GROUP BY month, country;
  -- #leetcode submit region end(Prohibit modification and deletion)
  