Monthly Transactions II

Monthly Transactions II

What is this problem about?

The Monthly Transactions II problem is a more complex SQL challenge where transactions can be "chargeback" — reversed after being initially approved. You need to report, per month and country, the number of approved transactions, their total amount, the number of chargebacks, and their total amount. This Monthly Transactions II coding problem requires joining the transactions table with a chargebacks table and using conditional aggregation.

Why is this asked in interviews?

Wish asks this because it tests multi-table SQL joins combined with complex conditional aggregation — a realistic data reconciliation scenario common in fintech and e-commerce reporting. The database interview pattern requires correctly handling the month of the chargeback (not the original transaction) and distinguishing original approvals from chargebacks.

Algorithmic pattern used

UNION + GROUP BY with conditional aggregation. Create a unified table using UNION ALL: original approved transactions (with their month) and chargebacks (with the chargeback month from the chargebacks table). For each row, tag whether it's "approved" or "chargeback." Then GROUP BY month and country, applying SUM(CASE WHEN ...) to separate the two categories.

Example explanation

Transactions: ID=1, approved, 2019-01, US, $100. ID=2, declined, 2019-01, UK,$50. Chargebacks: trans_id=1, date=2019-02. Report: month 2019-01/US: approved=1, amount=100, chargebacks=0, cb_amount=0. Report: month 2019-02/US: approved=0, amount=0, chargebacks=1, cb_amount=100.

Common mistakes candidates make

• Using the transaction's original date for the chargeback month (should use the chargeback's own date).
• Not joining chargebacks back to transactions to get country and amount.
• Counting declined transactions in the approved total.
• Not including months that only have chargebacks (they should still appear in output).

Interview preparation tip

Complex SQL problems with multiple event types (approved/chargeback) are best handled with UNION ALL to normalize all events into one table, then aggregate. This "event unification" pattern appears in A/B testing analytics, financial reconciliation, and user funnel reporting. Practice UNION ALL + conditional aggregation on multi-table scenarios — it's a high-value SQL skill for data engineering and analyst interviews.