Description
Write a SQL query to find monthly transaction statistics: count, total amount, approved count, approved amount. Group by month (YYYY-MM format) and country. **Output columns (in order):** month, country, trans_count, trans_total_amount, approved_count, approved_total_amount
transactions| Column | Type |
|---|---|
| id | INT |
| country | TEXT |
| state | TEXT (values: 'approved' or 'declined') |
| amount | INT |
| trans_date | TEXT (YYYY-MM-DD format) |
Examples
CREATE TABLE transactions (id INT, country TEXT, state TEXT, amount INT, trans_date TEXT); INSERT INTO transactions VALUES (1, 'US', 'approved', 1000, '2024-01-15'), (2, 'US', 'declined', 2000, '2024-01-20');2024-01|US|2|3000|1|1000US has 2 transactions totaling 3000. One was approved (1000), one declined (2000). Approved count: 1, approved amount: 1000.
CREATE TABLE transactions (id INT, country TEXT, state TEXT, amount INT, trans_date TEXT); INSERT INTO transactions VALUES (1, 'UK', 'approved', 750, '2024-02-05'), (2, 'UK', 'approved', 1250, '2024-02-12'), (3, 'DE', 'declined', 800, '2024-02-08'), (4, 'DE', 'declined', 600, '2024-02-18'), (5, 'DE', 'approved', 900, '2024-02-25');2024-02|DE|3|2300|1|900
2024-02|UK|2|2000|2|2000February 2024 statistics: UK has 2 transactions (both approved) totaling 2000, while Germany has 3 transactions (2 declined, 1 approved) totaling 2300 with only 900 from approved transactions.
CREATE TABLE transactions (id INT, country TEXT, state TEXT, amount INT, trans_date TEXT); INSERT INTO transactions VALUES (1, 'JP', 'declined', 500, '2024-03-01'), (2, 'JP', 'declined', 300, '2024-03-15'), (3, 'BR', 'approved', 1200, '2024-04-10'), (4, 'BR', 'declined', 800, '2024-04-20');2024-03|JP|2|800|0|0
2024-04|BR|2|2000|1|1200March 2024 Japan: 2 transactions (500+300=800 total), 0 approved, 0 approved amount. April 2024 Brazil: 2 transactions (1200+800=2000 total), 1 approved (1200), 1 declined (800). Use SUBSTR(trans_date,1,7) for month and SUM(CASE WHEN state='approved' THEN 1 ELSE 0 END) for approved counts.
Constraints
- •
Group by month and country