15. 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      |

.....

Like the course? Get enrolled and start learning!

basavaiah.t

· 2 months ago

Solution using row number approach:

WITH total_income AS
(
    SELECT
        account_id,
        DATE_FORMAT(day,'%Y-%m') AS month,
        SUM(amount) AS income
    FROM Transactions
    WHERE type = 'Creditor'
    GROUP BY account_id, DATE_FORMAT(day,'%Y-%m')
),

t2 AS
(
    SELECT
        t.account_id,
        t.month,
        t.income,
        a.max_income
    FROM total_income t
    JOIN Accounts a
        ON t.account_id = a.account_id
    WHERE t.income > a.max_income
),

t3 AS
(
    SELECT
        *,
        ROW_NUMBER() OVER
        (
            PARTITION BY account_id
            ORDER BY month
        ) AS rn
    FROM t2
)

SELECT DISTINCT account_id
FROM
(
    SELECT
        account_id,
        date_sub(
          str_to_date(concat(month,'-01'),'%Y-%m-%d'),