Home › Databases › SQL Practice Problems

Suspicious Bank Accounts

Problem

Table: Accounts

+----------------+------+
| Column Name    | Type |
+----------------+------+
| account_id     | int  |
| max_income     | int  |
+----------------+------+
account_id is the column with unique values for this table.
Each row contains information about the maximum monthly income for one bank account.

Table: Transactions

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| transaction_id | int      |
| account_id     | int      |
| type           | ENUM     |
| amount         | int      |
| day            | datetime |
+----------------+----------+
transaction_id is the column with unique values for this table.
Each row contains information about one transaction.
type is ENUM (category) type of ('Creditor','Debtor') where 'Creditor' means the user deposited money into their account and 'Debtor' means the user withdrew money from their account.
amount is the amount of money deposited/withdrawn during the transaction.

Problem Definition

A bank account is suspicious if the total income exceeds the max_income for this account for two or more consecutive months. The total income of an account in some month is the sum of all its deposits in that month (i.e., transactions of the type 'Creditor').

Write a solution to report the IDs of all suspicious bank accounts.

Return the result table in any order.

Example

Output

Try It Yourself

java

-- TODO: Write your user queries here

Solution

To identify suspicious bank accounts based on their transaction histories, we need to analyze the Accounts and Transactions tables. A bank account is deemed suspicious if it meets below condition:

Total Income Exceeds max_income for Two or More Consecutive Months:
- Total Income for a month is the sum of all deposits ('Creditor' transactions) made into the account during that month.
- The Total Income must exceed the account's max_income for two or more consecutive months.

Approach Overview

Calculate Monthly Total Income for Each Account: Aggregate 'Creditor' transactions by account_id and month to determine the Total Income for each account per month.
Identify Months Where Total Income Exceeds max_income: Compare the Total Income against the max_income for each account and mark the months where the income exceeds the threshold.
Determine Consecutive Months of Exceeding Income: Analyze the marked months to find accounts that have two or more consecutive months where their Total Income exceeded max_income.
Retrieve Suspicious Account IDs: Select the account_ids of accounts that meet the criteria.

SQL Query

WITH incomes AS (
    SELECT 
        a.account_id,
        DATE_FORMAT(day, '%Y-%m-01') AS trans_month,
        SUM(amount) AS total_income,
        b.max_income
    FROM 
        Transactions a
    INNER JOIN 
        Accounts b ON a.account_id = b.account_id
    WHERE 
        type = 'Creditor'
    GROUP BY 
        a.account_id, DATE_FORMAT(day, '%Y-%m-01'), b.max_income
    HAVING 
        IF(SUM(amount) > b.max_income, 1, 0) = 1
),
consec_income AS (
    SELECT 
        account_id,
        TIMESTAMPDIFF(
            MONTH, 
            LAG(trans_month, 1) OVER (
                PARTITION BY account_id 
                ORDER BY trans_month
            ), 
            trans_month
        ) AS prev_month
    FROM 
        incomes
)
SELECT DISTINCT 
    account_id
FROM 
    consec_income
WHERE 
    prev_month = 1;

Step-by-Step Approach

Step 1: Calculate Monthly Total Income for Each Account (`incomes` CTE)

Compute the Total Income for each account for every month by summing up all 'Creditor' transactions.

SQL Query:

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

Explanation:

DATE_FORMAT(day, '%Y-%m-01') AS trans_month:
- Extracts the year and month from the day column to group transactions by the first day of each month.
SUM(amount) AS total_income:
- Calculates the total deposited amount ('Creditor' transactions) for each account per month.
JOIN Accounts b ON a.account_id = b.account_id:
- Associates each transaction with its corresponding account to access the max_income.
WHERE type = 'Creditor':
- Filters transactions to include only deposits.
GROUP BY a.account_id, DATE_FORMAT(day, '%Y-%m-01'), b.max_income:
- Groups the data by account_id and month to aggregate the deposits.
HAVING IF(SUM(amount) > b.max_income, 1, 0) = 1:
- Retains only those groups where the Total Income exceeds the max_income.

Output After Step 1:

+------------+------------+--------------+------------+
| account_id | trans_month| total_income | max_income |
+------------+------------+--------------+------------+
| 3          | 2021-05-01 | 0            | 21000      |
| 3          | 2021-06-01 | 298000       | 21000      |
| 3          | 2021-07-01 | 64900        | 21000      |
| 4          | 2021-05-01 | 49300        | 10400      |
| 4          | 2021-06-01 | 10400        | 10400      |
| 4          | 2021-07-01 | 56300        | 10400      |
+------------+------------+--------------+------------+

Step 2: Determine Consecutive Months of Exceeding Income (`consec_income` CTE)

Identify accounts that have two or more consecutive months where their Total Income exceeded max_income.

SQL Query:

WITH incomes AS (
    SELECT 
        a.account_id,
        DATE_FORMAT(day, '%Y-%m-01') AS trans_month,
        SUM(amount) AS total_income,
        b.max_income
    FROM 
        Transactions a
    INNER JOIN 
        Accounts b ON a.account_id = b.account_id
    WHERE 
        type = 'Creditor'
    GROUP BY 
        a.account_id, DATE_FORMAT(day, '%Y-%m-01'), b.max_income
    HAVING 
        IF(SUM(amount) > b.max_income, 1, 0) = 1
),
consec_income AS (
    SELECT 
        account_id,
        TIMESTAMPDIFF(
            MONTH, 
            LAG(trans_month, 1) OVER (
                PARTITION BY account_id 
                ORDER BY trans_month
            ), 
            trans_month
        ) AS prev_month
    FROM 
        incomes
)
SELECT *
FROM consec_income;

Explanation:

LAG(trans_month, 1) OVER (PARTITION BY account_id ORDER BY trans_month):
- Retrieves the previous month's trans_month for each account_id.
TIMESTAMPDIFF(MONTH, previous_month, current_month) AS prev_month:
- Calculates the difference in months between the current month and the previous month.
- A result of 1 indicates that the current month is exactly one month after the previous month, signifying consecutive months.
consec_income CTE:
- Contains each account_id and the difference in months (prev_month) between consecutive entries where income exceeded max_income.

Output After Step 2:

+------------+------------+
| account_id | prev_month |
+------------+------------+
| 3          | NULL       |
| 3          | 1          |
| 4          | NULL       |
| 4          | 2          |
+------------+------------+

Explanation of Output:

Account 3:
- First Entry (2021-06-01): No previous month → prev_month = NULL.
- Second Entry (2021-07-01): Previous month = 2021-06-01 → prev_month = 1 (Consecutive).
Account 4:
- First Entry (2021-05-01): No previous month → prev_month = NULL.
- Second Entry (2021-07-01): Previous month = 2021-05-01 → prev_month = 2 (Not consecutive).

Step 3: Retrieve Suspicious Account IDs

Select the account_ids of accounts that have two or more consecutive months where their Total Income exceeded max_income.

SQL Query:

WITH incomes AS (
    SELECT 
        a.account_id,
        DATE_FORMAT(day, '%Y-%m-01') AS trans_month,
        SUM(amount) AS total_income,
        b.max_income
    FROM 
        Transactions a
    INNER JOIN 
        Accounts b ON a.account_id = b.account_id
    WHERE 
        type = 'Creditor'
    GROUP BY 
        a.account_id, DATE_FORMAT(day, '%Y-%m-01'), b.max_income
    HAVING 
        IF(SUM(amount) > b.max_income, 1, 0) = 1
),
consec_income AS (
    SELECT 
        account_id,
        TIMESTAMPDIFF(
            MONTH, 
            LAG(trans_month, 1) OVER (
                PARTITION BY account_id 
                ORDER BY trans_month
            ), 
            trans_month
        ) AS prev_month
    FROM 
        incomes
)
SELECT DISTINCT 
    account_id
FROM 
    consec_income
WHERE 
    prev_month = 1;

Explanation:

WHERE prev_month = 1:
- Filters the consec_income CTE to include only those records where the current month is exactly one month after the previous month, indicating consecutive months.
SELECT DISTINCT account_id:
- Ensures that each suspicious account_id appears only once in the final output, even if there are multiple pairs of consecutive months.

Output After Step 3:

+------------+
| account_id |
+------------+
| 3          |
+------------+

🤖 Don't fully get this? Learn it with Claude

Stuck on Suspicious Bank Accounts? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.

🎨 Explain it visually

Build the mental picture, not memorization.

I just read a lesson on **Suspicious Bank Accounts** (Databases) and want to truly understand it. Explain Suspicious Bank Accounts from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🤔 Walk me through it (interactive)

Socratic — adapts to where you're stuck.

Teach me **Suspicious Bank Accounts** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧪 Quiz me & fix my gaps

Active recall exposes what you missed.

Quiz me on **Suspicious Bank Accounts** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧠 Make it stick

Intuition + hook + flashcards for long-term memory.

Help me remember **Suspicious Bank Accounts** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

📝 My notes

← Find Interview Candidates Trips and Users →

Suspicious Bank Accounts

Problem

Problem Definition

Example

Output

Try It Yourself

Solution

Approach Overview

SQL Query

Step-by-Step Approach

Step 1: Calculate Monthly Total Income for Each Account (incomes CTE)

Step 2: Determine Consecutive Months of Exceeding Income (consec_income CTE)

Step 3: Retrieve Suspicious Account IDs

📝 My notes

Step 1: Calculate Monthly Total Income for Each Account (`incomes` CTE)

Step 2: Determine Consecutive Months of Exceeding Income (`consec_income` CTE)