Problem Description –
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
Write an SQL query to report the IDs of all suspicious bank accounts.
Return the result table ordered by
transaction_id in ascending order.
The query result format is in the following example.
Problem Link – Suspicious Bank Account
Difficulty Level – Medium
WITH temp AS ( SELECT t.account_id, DATE_FORMAT(day,'%Y%m') AS date, SUM(amount) AS 'income', Accounts.max_income FROM Transactions t LEFT JOIN Accounts ON Accounts.account_id=t.account_id WHERE t.type='Creditor' GROUP BY t.account_id, DATE_FORMAT(day,'%Y%m') HAVING SUM(amount)>Accounts.max_income ) SELECT t1.account_id FROM temp t1, temp t2 WHERE t1.account_id=t2.account_id AND PERIOD_DIFF(t1.date, t2.date)=1 GROUP BY t1.account_id ORDER BY t1.account_id