Leetcode Problem 1843. Suspicious Bank Accounts

1843. Suspicious Bank Accounts

Leetcode Solutions

MySQL CTE and PERIOD_DIFF

Create a CTE (temp) to calculate the total income per month for each account by summing up the amounts of 'Creditor' transactions grouped by account and month.
Join the temp CTE with the Accounts table to filter out the months where the income does not exceed the max_income.
Use a self-join on the temp CTE to find pairs of rows with the same account_id and consecutive months (using PERIOD_DIFF).
Group by account_id and select the accounts that appear more than once, indicating at least two consecutive months of exceeding max_income.
Return the distinct account_ids of the suspicious accounts.


erDiagram
    Accounts {
        int account_id
        int max_income
    }
    Transactions {
        int transaction_id
        int account_id
        enum type
        int amount
        datetime day
    }
    Accounts ||--o{ Transactions : has

MySQL CTE with Year-Month Grouping and Self-Join

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 1843. Suspicious Bank Accounts

Leetcode Solutions

MySQL CTE and PERIOD_DIFF

Solution Idea

Query Steps

Code Implementation

MySQL CTE with Year-Month Grouping and Self-Join

Ask Question

Suggested Answer