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 |
+---------+---------+-------------+----------------+--------------------+-----------------------+View Reference Solution & Explanation ▼
This solution uses the `GROUP BY` clause to aggregate results, a `CASE` statement for conditional logic, and date functions to handle time-based filtering or grouping.
SELECT STRFTIME('%Y-%m', trans_date) AS month, country, COUNT(*) 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 GROUP BY month, country;