Problem of The Day: Suspicious Bank Accounts

5 min read 1185 words

Problem Statement

leetcode problem link

note: hard problem - need to review T-SQL

Solution

-- CTE to calculate monthly income per account and keep only months exceeding max_income
WITH MonthlyIncome AS
(
    SELECT
        t.account_id,
        CONVERT(char(6), t.[day], 112) AS income_month, -- 'YYYYMM'
        SUM(t.amount) AS monthly_income,
        a.max_income
    FROM dbo.Transactions AS t
    LEFT JOIN dbo.Accounts AS a
        ON a.account_id = t.account_id
    WHERE t.[type] = 'Creditor'
    GROUP BY
        t.account_id,
        CONVERT(char(6), t.[day], 112),
        a.max_income
    HAVING SUM(t.amount) > a.max_income
)

-- Find accounts where this happens in two consecutive months
SELECT
    mi1.account_id
FROM MonthlyIncome AS mi1
JOIN MonthlyIncome AS mi2
    ON mi1.account_id = mi2.account_id
   AND DATEADD(month, 1, CONVERT(date, mi2.income_month + '01', 112))
       = CONVERT(date, mi1.income_month + '01', 112)
GROUP BY
    mi1.account_id
ORDER BY
    mi1.account_id;

Goal: Return account_id values where an account’s credited income exceeds max_income in two consecutive months.

---

1) What the query is made of

The query has two main parts:

1. A CTE named MonthlyIncome that calculates monthly credited totals per account and keeps only months where the total is above max_income.
2. A self-join on that CTE to detect back-to-back (consecutive) months.

---

2) Part 1 — Build MonthlyIncome (the CTE)

2.1) Filter to only “Creditor” transactions

We only care about income-like transactions:

• WHERE t.[type] = 'Creditor'

So debits/other types are ignored.

2.2) Convert each transaction date into a month key (YYYYMM)

We need to group by month, so we turn the date into a string like 202603:

• CONVERT(char(6), t.[day], 112) AS income_month

Explanation:

• Style 112 formats a date as YYYYMMDD
• char(6) keeps only YYYYMM

2.3) Join Accounts to get the threshold (max_income)

We join transactions to accounts:

• LEFT JOIN dbo.Accounts a ON a.account_id = t.account_id

This allows us to compare each month’s total income against a.max_income.

2.4) Group by account and month, then sum amounts

We calculate the monthly total:

• SUM(t.amount) AS monthly_income

And we group by:

• account_id
• income_month
• a.max_income (so it can be selected alongside the aggregate)

Result at this point: one row per (account_id, month) with the monthly credited total.

2.5) Keep only months where income exceeded the threshold

We filter aggregated results using HAVING:

• HAVING SUM(t.amount) > a.max_income

So the CTE output contains only “bad months” (months where the account exceeded max_income).

CTE output shape (conceptually):

• account_id
• income_month (like YYYYMM)
• monthly_income
• max_income

But only for months where monthly_income > max_income.

---

3) Part 2 — Find consecutive “bad months”

Now we want accounts where there exist two rows in MonthlyIncome that are one month apart.

3.1) Self-join the CTE

We reference the same CTE twice:

• MonthlyIncome mi1
• MonthlyIncome mi2

Join condition starts with:

• mi1.account_id = mi2.account_id

So we only compare months within the same account.

3.2) Compare months as real dates to check “next month”

Because income_month is YYYYMM, we convert it into a proper date by adding '01' (first day of that month):

• CONVERT(date, miX.income_month + '01', 112)

Then we check consecutiveness like this:

• DATEADD(month, 1, mi2_month_date) = mi1_month_date

Meaning:

• If you add 1 month to mi2’s month, you land exactly on mi1’s month.
• That’s how the query detects consecutive months.

Example:

• mi2 = 202501 → +1 month = 202502 → matches mi1 = 202502

3.3) Return each matching account once

A single account might have multiple consecutive pairs (e.g., Jan–Feb and Feb–Mar).
To return each account only once:

• GROUP BY mi1.account_id

3.4) Sort the output

Finally:

• ORDER BY mi1.account_id

---

4) What it returns (plain English)

The output is a list of account_ids where:

• Month M: total credited amount > max_income
• Month M+1: total credited amount > max_income

If the account exceeds in non-adjacent months only (e.g., January and March), it will not be returned.

Editorial

-- Common Table Expression (CTE) to calculate monthly income and compare with max_income
WITH MonthlyIncome AS (
  SELECT
    t.account_id,
    DATE_FORMAT(t.day, '%Y%m') AS income_month,
    -- Format transaction date to 'YYYYMM'
    SUM(t.amount) AS monthly_income,
    -- Calculate total income for the month
    a.max_income -- Include max_income from Accounts table
  FROM
    Transactions t
    LEFT JOIN Accounts a ON a.account_id = t.account_id -- Join with Accounts table
  WHERE
    t.type = 'Creditor' -- Consider only 'Creditor' transactions
  GROUP BY
    t.account_id,
    income_month
  HAVING
    SUM(t.amount) > a.max_income -- Filter months where income exceeds max_income
    ) -- Final query to find accounts with excessive income for two consecutive months
SELECT
  Income1.account_id
FROM
  MonthlyIncome Income1,
  MonthlyIncome Income2
WHERE
  Income1.account_id = Income2.account_id -- Compare the same account
  AND PERIOD_DIFF(
    Income1.income_month, Income2.income_month
  ) = 1 -- Check for consecutive months
GROUP BY
  Income1.account_id
ORDER BY
  Income1.account_id;