Monthly Transactions I

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a table of financial transactions. Each transaction has a country, a state (either 'approved' or 'declined'), an amount, and a date. Write a SQL query to find for each month and country: the total number of transactions, the number of approved transactions, the total transaction amount, and the total approved transaction amount. Return the result in any order.

Database Schema

Transactions

Column Name	Type	Description
id	INT	Primary key
country	VARCHAR	Country of transaction
state	ENUM	'approved' or 'declined'
amount	INT	Transaction amount
trans_date	DATE	Date of transaction

Example

Transactions

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

Explanation:

Group by month (YYYY-MM) and country. Count all transactions, count approved ones, sum all amounts, sum approved amounts.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.