1193. Monthly Transactions I - Detailed Explanation

Problem Statement

You are given a table Transactions with the following schema:

Column	Type	Description
id	int	Unique identifier of the transaction
country	varchar	Two‐letter country code where the transaction occurred
state	enum	One of `'approved'` or `'declined'`
amount	int	Transaction amount in whole dollars
trans_date	date	Date when the transaction took place

Write a single SQL query that, for each month (in "YYYY‑MM" format) and each country, computes:

trans_count: total number of transactions in that month and country
approved_count: number of transactions with state = 'approved'
trans_total_amount: sum of amount for all transactions in that month and country
approved_total_amount: sum of amount only for approved transactions in that month and country

Return the results with columns (month, country, trans_count, approved_count, trans_total_amount, approved_total_amount) in any order.

Examples

Example 1

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

Result:

month	country	trans_count	approved_count	trans_total_amount	approved_total_amount
2018‑12	US	2	1	3000	1000
2019‑01	US	1	1	2000	2000
2019‑01	DE	1	1	2000	2000

Hints

How can you extract just the year and month from a DATE in MySQL?
Which aggregation lets you count only when a condition (e.g. state='approved') holds?
How do you keep your query to a single GROUP BY for both overall and approved metrics?

Solution Approach

Formatting the Month

Use DATE_FORMAT(trans_date, '%Y-%m') (or SUBSTR(trans_date,1,7)) to turn a date like '2018-12-18' into '2018-12'.

Conditional Aggregation

COUNT(*) → total transactions
SUM(CASE WHEN state='approved' THEN 1 ELSE 0 END) → approved transaction count
SUM(amount) → total amount
SUM(CASE WHEN state='approved' THEN amount ELSE 0 END) → approved amount

Putting It Together

Group by the computed month and country in one pass.

SQL Query

SELECT
  DATE_FORMAT(trans_date, '%Y-%m') 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;

Complexity Analysis

Time: O(N) — a single scan of the Transactions table plus grouping work.
Space: O(M) for M distinct (month,country) groups.

Step‑by‑Step Walkthrough

Using the example data:

Extract months:
- '2018‑12‑18' → '2018‑12'
- '2019‑01‑01' → '2019‑01'
Group rows by ('2018‑12','US'), ('2019‑01','US'), ('2019‑01','DE').
Aggregate per group:
- For ('2018‑12','US'):
  - trans_count = 2
  - approved_count = 1
  - trans_total_amount = 1000+2000 = 3000
  - approved_total_amount = 1000
Return the three summary rows.

Python Code

Python3

. . . .

Java Code

Java

. . . .

Common Mistakes

Forgetting to format the date → grouping by full DATE yields daily buckets.
Using COUNT(state='approved') (invalid) instead of a proper CASE/SUM.
Omitting ELSE 0 in the approved‐amount sum, causing NULL when no approved rows exist.

Edge Cases

No rows → returns an empty result.
All declined for a (month,country) → approved_count = 0, approved_total_amount = 0.
Single row per group → simple counts of 1 and sums of that amount.

Alternative Variations

Change granularity to daily or quarterly by adjusting the date‐format string.
Include average transaction amount:
```
AVG(amount) AS avg_trans_amount
```
Pivot per state (approved vs. declined) using CASE with SUM or COUNT.