I have a table that store total account of a product every last day of a month. From that table, I want to calculate how much the account growing in percentage. My table looks like this

And below is my query for calculating the growth percentage

SELECT *
       ,  DIFF / ACC_YSTR GROWTH_PERCENTAGE
FROM(
SELECT *
       , (ACCOUNT - ACC_YSTR) DIFF
FROM(
SELECT PERIOD
       , GROUP_PRODUCT
       , ACCOUNT
       , COALESCE(LAG(ACCOUNT) OVER (PARTITION BY GROUP_PRODUCT ORDER BY PERIOD ASC), ACCOUNT) ACC_YSTR
FROM BASE_TABLE
))

But the problem is, my result for the growth percentage always resulting in 0. How to get the right result?

This is my dbfiddle: https://dbfiddle.uk/7kWmsvxM

CodePudding user response：

WITH BASE_TABLE(PERIOD, GROUP_PRODUCT, ACCOUNT) AS(
VALUES
(DATE('2022-01-31'), 'DEPOSITO',    22860),
(DATE('2022-02-26'), 'DEPOSITO',    22848),
(DATE('2022-03-31'), 'DEPOSITO',    22555),
(DATE('2022-04-30'), 'DEPOSITO',    22297),
(DATE('2022-05-31'), 'DEPOSITO',    22281)
)

SELECT *
       ,  1.0*DIFF / ACC_YSTR GROWTH_PERCENTAGE
FROM(
SELECT *
       , (ACCOUNT - ACC_YSTR) DIFF
FROM(
SELECT PERIOD
       , GROUP_PRODUCT
       , ACCOUNT
       , COALESCE(LAG(ACCOUNT) OVER (PARTITION BY GROUP_PRODUCT ORDER BY PERIOD ASC), ACCOUNT) ACC_YSTR
FROM BASE_TABLE
))

Fiddle