[LeetCode] 1193. Monthly Transactions I

 

 

[문제]

이미지를 누르면 문제 링크로 넘어갑니다

 

 

[MySQL]

WITH Transactions_preprocessed AS (
    SELECT *
        , DATE_FORMAT(trans_date, '%Y-%m') AS trans_month
    FROM Transactions
)

SELECT trans_month AS month
     , country
     , COUNT(DISTINCT id) AS trans_count
     , COUNT(DISTINCT CASE WHEN state = 'approved' THEN id END) AS approved_count
     , SUM(amount) AS trans_total_amount
     , SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions_preprocessed
GROUP BY trans_month, country