Monthly Transactions II - Practice Coding Problems

Monthly Transactions II - Problem

Database Medium

You're working as a data analyst for a global payment processing company that needs to generate comprehensive monthly financial reports. Your task is to analyze transaction data alongside chargeback information to provide insights into business performance.

You have access to two critical datasets:

Transactions table: Contains all payment transactions with their approval status, amounts, countries, and dates
Chargebacks table: Contains dispute information where customers have contested previous transactions

Your goal is to create a monthly summary report that shows, for each month-country combination:

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

Important notes:

Chargebacks can occur for any transaction (approved or declined)
The chargeback date determines which month it belongs to (not the original transaction date)
Exclude rows where all metrics are zero
Group results by YYYY-MM format and country

Input & Output

example_1.sql — Basic Example

$ Input: 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 | +----+---------+---------+--------+------------+ Chargebacks table: +----------+------------+ | trans_id | trans_date | +----------+------------+ | 121 | 2019-01-05 | | 122 | 2019-01-06 | +----------+------------+

› Output: +-------+---------+----------------+-----------------+-----------------+------------------+ | month | country | approved_count | approved_amount | chargeback_count| chargeback_amount| +-------+---------+----------------+-----------------+-----------------+------------------+ | 2018-12| US | 1 | 1000 | 0 | 0 | | 2019-01| DE | 1 | 2000 | 0 | 0 | | 2019-01| US | 1 | 2000 | 2 | 3000 | +-------+---------+----------------+-----------------+-----------------+------------------+

💡 Note: In December 2018, US had 1 approved transaction. In January 2019, US had 1 approved transaction plus 2 chargebacks (one from approved tx 121, one from declined tx 122). Germany had 1 approved transaction in January with no chargebacks.

example_2.sql — Same Month Scenario

$ Input: Transactions table: +----+---------+---------+--------+------------+ | id | country | state | amount | trans_date | +----+---------+---------+--------+------------+ | 101| CA | approved| 500 | 2019-02-15 | | 102| CA | approved| 800 | 2019-02-20 | +----+---------+---------+--------+------------+ Chargebacks table: +----------+------------+ | trans_id | trans_date | +----------+------------+ | 101 | 2019-02-25 | +----------+------------+

› Output: +-------+---------+----------------+-----------------+-----------------+------------------+ | month | country | approved_count | approved_amount | chargeback_count| chargeback_amount| +-------+---------+----------------+-----------------+-----------------+------------------+ | 2019-02| CA | 2 | 1300 | 1 | 500 | +-------+---------+----------------+-----------------+-----------------+------------------+

💡 Note: Both the approved transactions and the chargeback happened in February 2019, so they're aggregated together in the same month-country row.

example_3.sql — Edge Case: Only Chargebacks

$ Input: Transactions table: +----+---------+---------+--------+------------+ | id | country | state | amount | trans_date | +----+---------+---------+--------+------------+ | 201| FR | declined| 1500 | 2018-11-10 | | 202| FR | declined| 900 | 2018-11-15 | +----+---------+---------+--------+------------+ Chargebacks table: +----------+------------+ | trans_id | trans_date | +----------+------------+ | 201 | 2019-03-10 | | 202 | 2019-03-12 | +----------+------------+

› Output: +-------+---------+----------------+-----------------+-----------------+------------------+ | month | country | approved_count | approved_amount | chargeback_count| chargeback_amount| +-------+---------+----------------+-----------------+-----------------+------------------+ | 2019-03| FR | 0 | 0 | 2 | 2400 | +-------+---------+----------------+-----------------+-----------------+------------------+

💡 Note: France had no approved transactions in March 2019, but had 2 chargebacks totaling 2400. Since there's activity (chargeback_count > 0), this row is included in results.

Visualization

Tap to expand

Understanding the Visualization

Capture Events

Every approved transaction and chargeback is an event with date, country, and amount

Group by Time & Location

Aggregate events by month-country combinations using conditional counting

Generate Report

Output comprehensive monthly statements showing both positive and negative cash flows

Key Takeaway

🎯 Key Insight: The optimal approach treats approved transactions and chargebacks as separate event streams that are unified through conditional aggregation, allowing us to generate comprehensive monthly financial reports efficiently.

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Single pass through transactions (n) and chargebacks (m) with efficient grouping

✓ Linear Growth

Space Complexity

O(k)

Space for result set where k is number of unique month-country combinations

✓ Linear Space

Constraints

1 ≤ transactions.length ≤ 10⁴
1 ≤ chargebacks.length ≤ 10³
1 ≤ id, trans_id, amount ≤ 10⁶
state is either 'approved' or 'declined'
country is a valid 2-letter country code
All dates are in YYYY-MM-DD format

Asked in

S Stripe 35 P PayPal 28 □ Square 22 V Visa 18 a Amazon 15

The optimal solution uses a unified CTE approach with conditional aggregation to process both approved transactions and chargebacks in a single pass. By creating one event stream and using CASE statements for different metrics, we achieve better performance while maintaining clean, readable SQL code.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries Approach	O(n + m)	O(k)	Use separate subqueries for transactions and chargebacks, then combine with UNION
Single CTE with Conditional Aggregation (Optimal)	O(n + m)	O(k)	Use one query with conditional aggregation to process both transactions and chargebacks efficiently

Nested Subqueries Approach — Algorithm Steps

Create subquery for approved transactions grouped by month-country
Create separate subquery for chargebacks grouped by month-country
UNION ALL the results together
Filter out rows where all values are zero

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter Approved

Extract only approved transactions and group by month-country

Join Chargebacks

Join chargebacks with original transactions and group by chargeback date

Union Results

Combine both result sets using UNION ALL

Final Aggregation

Sum the combined results and filter out zero rows

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Two separate table scans - one for transactions (n) and one for chargebacks (m)

✓ Linear Growth

Space Complexity

O(k)

Space for result set where k is number of unique month-country combinations

✓ Linear Space

Constraints

1 ≤ transactions.length ≤ 10⁴
1 ≤ chargebacks.length ≤ 10³
1 ≤ id, trans_id, amount ≤ 10⁶
state is either 'approved' or 'declined'
country is a valid 2-letter country code
All dates are in YYYY-MM-DD format

28.4K Views

High Frequency

~25 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Nested Subqueries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler