Suspicious Bank Accounts

Mechanism

The query works by collapsing raw transactions into one row per account-month of deposits, throwing away every month that does not breach max_income, and then asking a single question of the survivors: for each over-limit month, was the previous over-limit month exactly one calendar month earlier? If LAG reaches back to a row that is one month away, those two surviving rows were adjacent in time, which is the definition of "two consecutive over-limit months" — and the account is flagged.

The subtle part is that consecutiveness is checked after filtering, on a table that contains only breach months. So adjacency in that filtered table means adjacency in real time only because every non-breach month was deleted first. TIMESTAMPDIFF(MONTH, prev, curr) = 1 is what verifies the two surviving rows really are back-to-back calendar months and not just neighbours in a sparse list.

The schema

Accounts(account_id PK, max_income) — one income ceiling per account. Transactions(transaction_id PK, account_id, type ENUM('Creditor','Debtor'), amount, day DATETIME) — 'Creditor' is a deposit (counts toward income), 'Debtor' is a withdrawal (ignored). An account is suspicious when its monthly deposit total exceeds max_income for two or more consecutive months.

A worked example with real values

Take two accounts. The Debtor rows are noise — they exist only to prove the WHERE type = 'Creditor' filter matters.

Accounts

Transactions (deposits unless marked)

Note account 3 has a May row, but it is a Debtor, so May contributes zero income and never appears once we filter to deposits. That is the correct reason May vanishes — not a phantom "0 income" row that somehow passed a strict "> 21000" test.

Step 1 — monthly deposit totals, keeping only the breaches

WITH incomes AS (
  SELECT a.account_id,
         DATE_FORMAT(a.day, '%Y-%m-01') AS trans_month,
         SUM(a.amount)                 AS total_income,
         b.max_income
  FROM Transactions a
  JOIN Accounts b ON a.account_id = b.account_id
  WHERE a.type = 'Creditor'
  GROUP BY a.account_id, DATE_FORMAT(a.day, '%Y-%m-01'), b.max_income
  HAVING SUM(a.amount) > b.max_income
)
SELECT * FROM incomes;

The HAVING runs after aggregation and keeps a group only when its summed deposits strictly exceed max_income. Every row below survives that test — that is the whole point of showing this table.

Output after Step 1 (only breach months — verify each row yourself):

Why the rows the old page showed were wrong. The previous version printed 3 | 2021-05-01 | 0 | 21000 and 4 | 2021-06-01 | 10400 | 10400. Neither can exist in this CTE: a total of 0 is not > 21000, and 10400 is not > 10400 (the comparison is strict, so equal does not pass). Both fail HAVING and are discarded before the table is ever produced. Account 3's May produced no group at all because its only May transaction was a withdrawal. Printing those phantom rows made the intermediate output contradict the query that generated it.

Step 2 — measure the gap to the previous breach

consec_income AS (
  SELECT account_id,
         TIMESTAMPDIFF(
           MONTH,
           LAG(trans_month) OVER (PARTITION BY account_id ORDER BY trans_month),
           trans_month
         ) AS gap
  FROM incomes
)
SELECT * FROM consec_income;

LAG walks each account's breach months in date order and hands back the prior breach month; TIMESTAMPDIFF(MONTH, prev, curr) turns the pair into an integer month gap. A gap of exactly 1 means two breaches sat in back-to-back calendar months.

Output after Step 2 (computed only from the four surviving Step-1 rows):

Account 3's July is one month after its June breach → gap = 1. Account 4's two breaches are May and July; June did not breach so it is absent, and LAG jumps straight from July back to May → gap = 2. The vanished June is exactly why the gap is 2, not 1.

Step 3 — keep accounts with any 1-month gap

SELECT DISTINCT account_id
FROM consec_income
WHERE gap = 1;

WHERE gap = 1 discards the NULL first-row gaps and any wider jumps, keeping only accounts that had at least one pair of consecutive breach months. DISTINCT collapses an account that breached three-plus months in a row (which would yield multiple gap = 1 rows) down to one id.

Final result:

Account 4 is correctly excluded: it breached twice, but not in adjacent months.

Pitfalls

• Showing intermediate rows that the query itself rejects. The original page printed a 0-income May row and a 10400 = 10400 June row in the Step-1 output even though both fail HAVING SUM(amount) > max_income. An intermediate-result table must be exactly what its query returns, or the whole trace stops being trustworthy.
• Strict vs. non-strict comparison. The rule is exceeds, so >, not >=. An account whose monthly deposits equal max_income is not breaching. Using >= would wrongly flag account 4's June and then report account 4 as suspicious.
• Forgetting to filter Debtors. Income is deposits only. If you drop WHERE type = 'Creditor', withdrawals inflate (or, with signed handling, distort) the monthly sum and you breach on phantom income. Account 3's May withdrawal is the trap row.
• Checking consecutiveness on row numbers instead of dates. A common wrong fix is LAG over a ROW_NUMBER sequence — that only tells you two breaches are adjacent in the filtered list, which is true for account 4's May→July and would falsely flag it. You must diff the actual trans_month values with TIMESTAMPDIFF so a skipped month registers as a gap of 2.
• Crossing a year boundary. Subtracting raw month numbers (12 for Dec vs 1 for Jan) gives -11, not 1. TIMESTAMPDIFF(MONTH, …) on full dates handles Dec→Jan as a gap of 1 correctly; naive month arithmetic does not.

Takeaways

• Filter to the breach months first, then test adjacency on the survivors — that two-phase shape (HAVING filter → window function) is the reusable pattern for "N consecutive periods meeting a condition."
• LAG(...) OVER (PARTITION BY key ORDER BY time) plus TIMESTAMPDIFF is the canonical way to ask "how far back was the previous qualifying event," and a gap of 1 is your consecutiveness signal.
• Because non-qualifying months are deleted before the gap is measured, a real skipped month surfaces as a gap > 1 — which is precisely what separates a true streak from two isolated breaches.
• An intermediate-output table is a contract: every row in it must be reproducible by running the shown query. If it is not, fix the table, not the narrative around it.

Problem from LeetCode 1843 “Suspicious Bank Accounts.” Mechanics of LAG, TIMESTAMPDIFF, DATE_FORMAT, and the HAVING-after-aggregation rule follow the MySQL 8.0 Reference Manual (window functions and date/time functions). Re-authored and deepened for this guide: the Step-1 intermediate output was corrected to show only rows that survive HAVING SUM(amount) > max_income (the previous version printed a 0-income and an equal-to-limit row that the query rejects), a self-consistent worked dataset and a timeline diagram were added, and a “why the naive row-number version is wrong” note was included.