Comparing Data set over a rolling window

Database
New Member

Comparing Data set over a rolling window

Hello,

I have a scenario wherein I have to compare data set over weekly periods  as below

BRANCH     ACCT NUM     ASOF_DATE   Balance

1               100               20180608      24000

1               100               20180615      24100

1               200               20180608      23487

1               200               20180615      30000

2               343               20180608      100

2               343               20180615      2309

 

My final data set I am trying to get is

BRANCH    , COUNT ( accounts which had More balance in a week than the prior week), SUM ( difference in balance where balance was more in 1 week than before)

Is there a way to do this ? Thanks !!

1 REPLY
Junior Contributor

Re: Comparing Data set over a rolling window

This should match your description:

SELECT branch, asof_date, Count(*), Sum(balance_diff)
FROM
 (
   SELECT branch, asof_date,
      balance -- calculate the difference between current and previous row 
      - Lag(balance)
        Over (PARTITION BY branch, acct_num
              ORDER BY asof_date) AS balance_diff
   FROM tab
   QUALIFY balance_diff > 0 -- return only rows with positive difference
 ) AS dt
 GROUP BY 1,2