Monthly Transactions II

The mechanism in one line

Reshape two differently-shaped event streams into one stream that carries a synthetic state label, then let a single grouped pass count and sum each label per bucket — so chargebacks and approvals are tallied side by side without ever joining the two aggregates back together.

That synthetic-label trick has a name worth keeping: a tagged union (also called a discriminated union or a UNPIVOT-by-UNION). You normalize heterogeneous rows to a common schema, add a discriminator column, stack them with UNION ALL, and then a conditional SUM(CASE …) demultiplexes them back into separate output columns. It is the SQL equivalent of switch on a tagged enum.

The trap that makes this problem "II"

A chargeback is not bucketed by the original transaction's date — it is bucketed by the chargeback's own trans_date, but it inherits the country and amount of the original transaction. So three independent facts about one transaction can land in three different months:

• The original approval counts in the month it was approved.
• The chargeback of that same transaction counts in the (later) month the chargeback arrived.
• A chargeback can hit a transaction that was declined or even approved in a month with zero other activity.

This is why the chargeback branch must JOIN Transactions (to recover country and amount) while taking its month from Chargebacks.trans_date.

The corrected query

The walkthrough's query is correct as written. Two details carry the whole problem: the chargeback branch dates by Chargebacks.trans_date (not the transaction's), and HAVING — not WHERE — does the zero-row filtering because it must run after aggregation.

SELECT s.month,
       s.country,
       SUM(CASE WHEN s.state = 'approved' THEN 1 ELSE 0 END)      AS approved_count,
       SUM(CASE WHEN s.state = 'approved' THEN s.amount ELSE 0 END) AS approved_amount,
       SUM(CASE WHEN s.state = 'back'     THEN 1 ELSE 0 END)      AS chargeback_count,
       SUM(CASE WHEN s.state = 'back'     THEN s.amount ELSE 0 END) AS chargeback_amount
FROM (
    -- chargeback branch: month from the CHARGEBACK, country/amount from the TRANSACTION
    SELECT LEFT(c.trans_date, 7) AS month,
           t.country,
           'back'                AS state,   -- synthetic discriminator
           t.amount
    FROM   Chargebacks c
    JOIN   Transactions t ON c.trans_id = t.id

    UNION ALL

    -- approval branch
    SELECT LEFT(t.trans_date, 7) AS month,
           t.country,
           t.state,
           t.amount
    FROM   Transactions t
    WHERE  t.state = 'approved'
) s
GROUP BY s.month, s.country
HAVING approved_count > 0 OR chargeback_count > 0
ORDER BY s.month;

Use single quotes for string literals ('back'): double quotes are standard-SQL identifiers, and on a database with ANSI_QUOTES enabled the original "back" would be parsed as a column name and error. MySQL's default mode happens to tolerate it, which is exactly the kind of portability landmine worth removing.

Worked trace on the real example

Inputs (the canonical dataset, all US):

Step 1 — approval branch (only approved rows survive; 102 and 104 are dropped here):

Step 2 — chargeback branch (month from the chargeback date; amount/country recovered via the join — note CB on 102 survives even though 102 was declined):

Step 3 — UNION ALL + GROUP BY + conditional SUM. The 2019-09 bucket has zero approvals but one chargeback, so HAVING keeps it. Every output reconciles with the stacked stream:

The 2019-06 approved_amount = 3000 + 5000 = 8000 confirms the conditional sum is adding only the rows whose discriminator matches.

Why the naive versions are wrong

• Two separate aggregates then JOIN. The tempting shape is: aggregate approvals by (month, country), aggregate chargebacks by (month, country), then JOIN them. But 2019-09 has a chargeback and no approval, and 2019-05 would in general have months that exist on only one side. An INNER JOIN silently drops these; you need a FULL OUTER JOIN with COALESCE on both sides — more code and more chances to forget a side. The tagged union sidesteps the outer-join entirely.
• Dating the chargeback by the transaction's date. Joining and then using LEFT(t.trans_date,7) puts the chargeback of txn 101 into 2019-05 instead of 2019-06, and txn 105's chargeback into 2019-06 instead of 2019-09 — the whole point of part "II" lost.
• Filtering zeros with WHERE. WHERE runs before grouping, so it cannot see approved_count. Putting the all-zero filter anywhere but HAVING is a syntax or logic error.
• Restricting the chargeback join to approved transactions. The spec says a chargeback can correspond to a transaction "even if it was not approved." Txn 102 was declined yet still produces a valid chargeback row; an AND t.state='approved' on the join would erase it.

Pitfalls

• UNION vs UNION ALL. Plain UNION deduplicates. Two genuinely identical (month, country, state, amount) rows — e.g. two 1000-amount approvals in the same US month — would collapse to one and undercount. Always UNION ALL for additive aggregation.
• Quoting strings as "…". Portable only by luck. Under ANSI_QUOTES / Postgres, double quotes mean identifier; use 'back'.
• Month via string slicing. LEFT(date,7) assumes YYYY-MM-DD text formatting; on engines that store/return dates differently, prefer DATE_FORMAT(d,'%Y-%m') (MySQL) or TO_CHAR(d,'YYYY-MM') (Postgres) to be format-independent.
• Schema mismatch in the union. The two SELECT branches must list columns in the same order and compatible types; UNION ALL matches by position, not by name, so a swapped country/state would compile and silently corrupt results.

Takeaways

• When you need parallel metrics over heterogeneous rows, tag-and-stack (UNION ALL with a discriminator) then demultiplex with conditional SUM(CASE) — it replaces a fragile FULL OUTER JOIN of two aggregates with one clean grouped pass.
• Conditional SUM(CASE WHEN … THEN 1/amount ELSE 0) is the generic SQL pivot: each output column is one branch of a switch over the discriminator.
• Know which date governs each fact — here the chargeback is bucketed by its own arrival date but carries the transaction's country and amount; mixing those up is the entire difficulty of this problem.
• HAVING is the only place a post-aggregation predicate (like "drop all-zero buckets") can live.

Problem from LeetCode 1645 "Hopper Company Queries"-family / "Monthly Transactions II"; canonical example dataset reproduced. Mechanism framing (tagged/discriminated union, conditional-aggregation pivot) draws on the SQL standard's treatment of UNION ALL and CASE, and on the conditional-aggregation pattern documented in the PostgreSQL and MySQL manuals (CASE, DATE_FORMAT/TO_CHAR) and in Itzik Ben-Gan's T-SQL Querying. Re-authored and deepened for this guide: added the mechanism statement, named the tagged-union pattern, traced the real example end-to-end, diagrammed the pipeline, fixed the string-literal quoting for portability, and documented the four naive-version failure modes.