Oracle Query to derive all Id’s whose balances have increased from consecutive 3 months along with there growth percent

I wanted to extract all id’s that have increased balances from last consecutive 3 months, and also display their growth percent

sample data set– not all Id’s will have consecutive 3 months of data. In that case derive growth percent from (what is available) most recent previous

ID   DATE    BAL
1   201904  81747.88
1   201905  90102.06
1   201906  131580.78
2   201904  47903.14
2   201905  51829.55
2   201906  57044.11
3   201904  43650.41
3   201905  47838.46
4   201906  204320.01
5   201905  528.92
5   201906  1158.07
6   201904  27236.02
6   201905  35019.5
6   201906  45228.87

Below is the sql i’m using to calculate the metrics, but something seem to be not working right, as my growth_prcnt is wrong. There should be a better way to derive this

    select (BAL-PREV_3MTNH_BAL)/PREV_3MTNH_BAL as grwth_prcnt, c.*  
    from(
      select b.* 
        from (
          select A.*, LAG(BAL, 3) over (partition by ID order by DATE) AS PREV_3MTNH_BAL,
            LAG(BAL, 2) over (partition by ID order by DATE) as PREV_2MTNH_BAL,
            LAG(BAL, 1) over (partition by ID order by DATE) as PREV_1MTNH_BAL
          from temp A ) b
        where BAL>PREV_3MTNH_BAL and  BAL>PREV_2MTNH_BAL AND BAL>PREV_1MTNH_BAL AND DATE >=201904)C

Answer

As far as I understood your problem, Following should be the query:

-- CONSECUTIVE 3 MONTH DATA MUST BE
-- CURRENT MONTH (JUNE)
-- LAST MONTH (MAY)
-- LAST TO LAST MONTH (APRIL)
SELECT
    -- MULTIPLIED BY 100 BECAUSE IT IS PERCENTAGE AS NAME SUGGEST
    ROUND(((BAL - PREV_2MTNH_BAL) * 100) / PREV_2MTNH_BAL, 2) AS GRWTH_PRCNT,
    B.*
FROM
    (
        SELECT
            A.*, -- CURRENT MONTH DATA AND BALANCE
            LAG(BAL, 1) OVER(
                PARTITION BY ID
                ORDER BY
                    DATE
            ) AS PREV_1MTNH_BAL, -- LAST MONTH BALANCE
            LAG(BAL, 2) OVER(
                PARTITION BY ID
                ORDER BY
                    DATE
            ) AS PREV_2MTNH_BAL -- LAST TO LAST MONTH BALANCE
        FROM
            TEMP A
    ) B
WHERE
    -- CURRENT MONTH BALANCE MUST BE GREATER THAN LAST MONTH BALANCE
    BAL > PREV_1MTNH_BAL 
    -- LAST MONTH BALANCE MUST BE GREATER THAN LAST TO LAST MONTH BALANCE
    AND PREV_1MTNH_BAL > PREV_2MTNH_BAL
    AND DATE >= 201904

Cheers!!