SQL MOSAIC #1193. Monthly Transactions I
Jul 5, 2022
Date:7/5/2022
Thinking Pathway:
- DATE_FORMAT() Function
Solution(s):
SELECT DATE_FORMAT(trans_date, ‘%Y-%m’) AS month,
country,
COUNT(id) AS trans_count,
SUM(
CASE WHEN state = ‘approved’ THEN 1
ELSE 0
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 t
GROUP BY month, country
