LeetCode：(SQL)(SQL 50)Monthly Transactions I

題目連結：https://leetcode.com/problems/monthly-transactions-i/description/?envType=study-plan-v2&envId=top-sql-50

題意解析

• 給定各個國家 Approve or Declined 的交易紀錄
• 計算出每個月份的交易量，同意交易量

解題思維

• state=’approved’ : True = 1, False = 0

實作程式碼

# Write your MySQL query statement below
SELECT
    DATE_FORMAT(trans_date,'%Y-%m') AS month
    ,country
    ,COUNT(1)                       AS trans_count
    # 不用 SUM(IF(state='approved',1,0)) state='approved' true = 1, false = 0
    ,SUM(state='approved')          AS approved_count
    ,SUM(amount)                    AS trans_total_amount
    ,SUM((state='approved')*amount) AS approved_total_amount
FROM Transactions
GROUP BY month
        ,country