CODEWITHSUNDEEP
sqloracle-databasefunctionaggregate
Amir
Amir

Reputation: 113

Accounts only if Balance is Negative for 3 months or more

Select * from Balance;

It has AccountID, ClosingDate and Balance.

Every Month it get an entry. For Example:

AccountID   ClosingDate   Balance
123         1/19/2017     -3.00
123         12/19/2016    -2.50
123         11/19/2016    -2.50

Another Account

AccountID   ClosingDate   Balance
456         1/16/2017     -3.00
456         12/16/2016    2.00
456         11/16/2016    -2.50

Last Example: 

AccountID   ClosingDate   Balance
789         1/11/2017     -9.00
789         12/11/2016    -2.00
789         11/11/2016    -5.50

I want to write a query that generates the list of accounts who are negative for consecutive 3 statement cycles or more. Keep in mind closing date is different for all of them. The query should not pick up the '456' Account Number. There are around 100,000 Accounts who are negative. But whether all of them are negative for consecutive 3 months is what I need to find.

I need the Account Number and Latest Balance as the output. However, if querying the latest balance would be difficult, then just the account number would suffice.

Thanks

Upvotes: 0

Views: 888

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

If your dbms supports window functions, use lead and lag to get the next and previous balances, first_value to get the latest balance per accountid. Then get those accounts with the latest balance where the current row's balance, next row's balance and previous row's balance are < 0.

with cte as (select t.*, 
             lead(balance) over(partition by accountid order by closingdate) as next_balance,
             lag(balance) over(partition by accountid order by closingdate) as prev_balance,
             first_value(balance) over(partition by accountid order by closingdate desc) as latest_balance
             from t)
select distinct accountid, latest_balance
from cte
where balance < 0 and next_balance < 0 and prev_balance < 0

Edit: Explanation of how it works. Let me show it with your sample data.

123         1/19/2017     -3.00 --balance = -3.00, previous_balance = -2.50, next_balance = null
123         12/19/2016    -2.50 --balance = -2.50, previous_balance = -2.50, next_balance = -3.00
123         11/19/2016    -2.50 --balance = -2.50, previous_balance = null, next_balance = -2.50
  • where balance < 0 and next_balance < 0 and prev_balance < 0 - The row on 12/19/2016 satisfies the criteria of negative balance for 3 consecutive months for this accountid and it is returned in the result.
  • first_value(balance) over(partition by accountid order by closingdate desc) - returns the balance as of the latest date for each account.

You can use this explanation to understand how it works for other accountid's.

Edit_2: To get the accounts which have negative balance for 7 or more consecutive months, use 

WITH CTE AS
 (SELECT T.*,
         FIRST_VALUE(BALANCE) OVER(PARTITION BY ACCOUNTID ORDER BY CLOSINGDATE DESC) AS LATEST_BALANCE ,
         ROW_NUMBER() OVER(PARTITION BY ACCOUNTID ORDER BY CLOSINGDATE) - 
         ROW_NUMBER() OVER(PARTITION BY ACCOUNTID, SIGN(BALANCE) ORDER BY CLOSINGDATE) AS GRP
  FROM T
  )
 ,COUNTS AS
 (SELECT C.*,
         SUM(CASE WHEN BALANCE < 0 THEN 1 ELSE 0 END) OVER(PARTITION BY ACCOUNTID,GRP ORDER BY CLOSINGDATE) AS CONSECUTIVE_COUNT
  FROM CTE C
  )
SELECT DISTINCT ACCOUNTID, LATEST_BALANCE
FROM COUNTS
WHERE CONSECUTIVE_COUNT >= 7

Or if you are prepared to write a lengthy query, use the optional parameter in the lead and lag function to look forward or look back n rows. Here you look back and look forward 3 rows each, for each row.

with cte as (select t.*, 
             lead(balance,1) over(partition by accountid order by closingdate) as next_balance_1,
             lead(balance,2) over(partition by accountid order by closingdate) as next_balance_2,
             lead(balance,3) over(partition by accountid order by closingdate) as next_balance_3,
             lag(balance,1) over(partition by accountid order by closingdate) as prev_balance_1,
             lag(balance,2) over(partition by accountid order by closingdate) as prev_balance_2,
             lag(balance,3) over(partition by accountid order by closingdate) as prev_balance_3,
             first_value(balance) over(partition by accountid order by closingdate desc) as latest_balance
             from t)
select distinct accountid, latest_balance
from cte
where balance < 0 
and next_balance_1 < 0 and next_balance_2 < 0 and next_balance_3 < 0
and prev_balance_1 < 0 and prev_balance_2 < 0 and prev_balance_3 < 0

Upvotes: 1

Related Questions