# SQL Interview Questions – Suspicious Bank Accounts

### 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 `'Creditor'`).

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.

## Solution –

``````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``````

Rating: 1 out of 5.

Posted in SQL