Monthly Transactions II - Problem

You have two tables: Transactions and Chargebacks.

The Transactions table contains information about incoming transactions with columns for id, country, state (approved/declined), amount, and trans_date.

The Chargebacks table contains information about chargebacks with trans_id (foreign key to Transactions.id) and trans_date (chargeback date).

Task: For each month and country, find:

  • Number of approved transactions and their total amount
  • Number of chargebacks and their total amount

Note: Ignore rows with all zeros. Chargebacks can occur for any transaction (approved or declined).

Table Schema

Transactions
Column Name Type Description
id PK int Primary key, unique transaction ID
country varchar Country where transaction occurred
state enum Transaction status: 'approved' or 'declined'
amount int Transaction amount
trans_date date Transaction date
Primary Key: id
Chargebacks
Column Name Type Description
trans_id PK int Foreign key referencing Transactions.id
trans_date date Chargeback date
Primary Key: trans_id

Input & Output

Example 1 — Basic Transactions and Chargebacks
Input Tables:
Transactions
id country state amount trans_date
1 US approved 1000 2019-05-18
2 US declined 2000 2019-06-17
3 US approved 3000 2019-06-17
4 US approved 4000 2019-06-17
Chargebacks
trans_id trans_date
2 2019-06-15
3 2019-06-15
Output:
month country approved_count approved_amount chargeback_count chargeback_amount
2019-05 US 1 1000 0 0
2019-06 US 2 7000 2 5000
💡 Note:

For 2019-05: 1 approved transaction (id=1) worth $1000, no chargebacks. For 2019-06: 2 approved transactions (id=3,4) worth $7000 total, and 2 chargebacks (for transactions 2 and 3) worth $5000 total. Note that transaction 2 was declined but still had a chargeback.
Example 2 — Multiple Countries
Input Tables:
Transactions
id country state amount trans_date
1 US approved 1000 2019-05-18
2 CA approved 2000 2019-05-18
3 CA declined 1500 2019-05-19
Chargebacks
trans_id trans_date
3 2019-06-01
Output:
month country approved_count approved_amount chargeback_count chargeback_amount
2019-05 CA 1 2000 0 0
2019-05 US 1 1000 0 0
2019-06 CA 0 0 1 1500
💡 Note:

Shows data for multiple countries. In May, US had 1 approved transaction ($1000) and CA had 1 approved transaction ($2000). In June, CA had a chargeback for the declined transaction 3 ($1500).

Constraints

  • 1 ≤ id ≤ 1000
  • country is a valid country code
  • state is either 'approved' or 'declined'
  • amount > 0
  • trans_date is a valid date

Visualization

Tap to expand
Monthly Transactions II INPUT id country state amt month 101 US approved 1000 2019-05 102 US declined 2000 2019-05 103 CA approved 3000 2019-05 104 US approved 4000 2019-06 Chargebacks Table trans_id date 102 2019-05-29 104 2019-07-01 Transactions + Chargebacks linked by trans_id ALGORITHM STEPS 1 JOIN Tables LEFT JOIN chargebacks on trans_id to get all data 2 GROUP BY month, country Aggregate data by period and location 3 Calculate Metrics COUNT/SUM for approved COUNT/SUM for chargebacks 4 Filter Zero Rows HAVING clause removes rows with all zeros SUM(CASE WHEN state='approved' THEN amount ELSE 0 END) as approved_total FINAL RESULT month ctry appr_cnt appr_amt chbk 2019-05 US 1 1000 1 2019-05 CA 1 3000 0 2019-06 US 1 4000 0 2019-07 US 0 0 1 OK - Stats aggregated! 4 rows with non-zero approved or chargeback data Legend: Approved trans Chargebacks Key Insight: Chargebacks use the ORIGINAL transaction's month/country, not the chargeback date's month. Use UNION ALL to combine approved transactions and chargebacks into unified aggregation, then filter out rows where both approved_count and chargeback_count are zero using HAVING. TutorialsPoint - Monthly Transactions II | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
34.5K Views
Medium Frequency
~20 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