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: Aggregation FlowInput: Raw TransactionscountrystateamountdateUSapproved10002018-12USdeclined20002018-12DEapproved20002019-01USapproved20002019-01GROUP BYmonth, countryConditional SUMOutput: Monthly Summarymonthcountrytrans_counttrans_totalapproved_count2018-12US2300012019-01DE1200012019-01US120001Key Operations:1. Extract month from trans_date using LEFT(trans_date::text, 7)2. GROUP BY month and country to create aggregation groups3. Use COUNT(*) and SUM(amount) for all transactions4. Use SUM(CASE WHEN state = 'approved') for conditional aggregation
Understanding the Visualization
1
Input Data
Individual transactions with dates and states
2
Group & Aggregate
GROUP BY month/country, conditional SUM
3
Monthly Summary
Aggregated transaction metrics per month-country
Key Takeaway
🎯 Key Insight: Use conditional aggregation with CASE WHEN to calculate multiple metrics (all vs approved) in a single GROUP BY query

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