Monthly Transactions I - Problem

Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.

The Transactions table contains:

  • id - Primary key
  • country - Transaction country
  • state - Either 'approved' or 'declined'
  • amount - Transaction amount
  • trans_date - Transaction date

Return the result table in any order.

Table Schema

Transactions
Column Name Type Description
id PK int Primary key
country varchar Country where transaction occurred
state enum Transaction state: 'approved' or 'declined'
amount int Transaction amount
trans_date date Date when transaction occurred
Primary Key: id
Note: Each row represents one transaction with approval status

Input & Output

Example 1 — Mixed Approved and Declined Transactions
Input 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 trans_total_amount approved_count approved_total_amount
2018-12 US 2 3000 1 1000
2019-01 DE 1 2000 1 2000
2019-01 US 1 2000 1 2000
💡 Note:

Groups transactions by month and country. For US in 2018-12: 2 total transactions (1 approved, 1 declined) with total amount 3000 and approved amount 1000.
Example 2 — All Declined Transactions
Input Table:
id country state amount trans_date
125 CA declined 1500 2019-02-15
126 CA declined 500 2019-02-20
Output:
month country trans_count trans_total_amount approved_count approved_total_amount
2019-02 CA 2 2000 0 0
💡 Note:

When all transactions are declined, approved_count and approved_total_amount are both 0, while trans_count and trans_total_amount include all transactions.

Constraints

  • 1 ≤ id ≤ 1000
  • state is either 'approved' or 'declined'
  • country contains only uppercase letters
  • 1 ≤ amount ≤ 10000

Visualization

Tap to expand
Monthly Transactions I INPUT Transactions Table id country state amount date 1 US approved 1000 2019-01-01 2 US declined 2000 2019-01-02 3 US approved 3000 2019-02-01 4 DE approved 4000 2019-01-15 Column Types: id: INT (PK) country: VARCHAR state: ENUM amount: INT trans_date: DATE Required Output: For each month + country: - Total transaction count - Total amount - Approved count - Approved amount ALGORITHM STEPS 1 Extract Month-Year DATE_FORMAT(trans_date, '%Y-%m') AS month 2 Group By Month + Country GROUP BY month, country 3 Count All Transactions COUNT(*) AS trans_count SUM(amount) AS trans_total 4 Filter Approved with CASE SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0) SELECT DATE_FORMAT(trans_date,'%Y-%m'), country, COUNT(*), SUM(amount), SUM(CASE WHEN state='approved' THEN 1 ELSE 0 END), SUM(CASE...amount...) FROM Transactions GROUP BY 1, 2 FINAL RESULT Output Table month ctry cnt total appr_cnt appr_amt 2019-01 US 2 3000 1 1000 2019-01 DE 1 4000 1 4000 2019-02 US 1 3000 1 3000 Aggregation Results: 2019-01, US: 2 trans (1 appr + 1 decl) Total: 3000, Approved: 1000 2019-02, US: 1 trans (1 approved) Query Complete! All transactions grouped by month and country with approved stats calculated Key Insight: Use CASE WHEN inside aggregate functions (SUM, COUNT) to conditionally include values. This technique allows calculating multiple conditional aggregates in a single GROUP BY query, avoiding the need for multiple subqueries or self-joins. DATE_FORMAT extracts year-month for grouping. TutorialsPoint - Monthly Transactions I | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Facebook 6
23.4K Views
High Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen