Percent Growth Calculation from Single Column

I am trying to calculate the percent growth of a single column (COUNT column) as shown below:

DATE        COUNT
2017-05-22  20
2017-05-29  42
2017-06-05  123
2017-06-12  21
2017-06-19  535
2017-06-26  3236
2017-07-03  32133

The following here is the desired output with a Percent Growth Column taking into consideration the weekly change of the middle COUNT column:

DATE        COUNT   Perc Growth
2017-05-22  20  
2017-05-29  42      110.00%
2017-06-05  123     192.86%
2017-06-12  21      -82.93%
2017-06-19  535     2447.62%
2017-06-26  3236    504.86%
2017-07-03  32133   892.99%

When i did the following line of SQL, this did not give me the correct values for the percent growth! Any advice?

SELECT DATE_TRUNC(WEEK, t.DATE_CREATED) as DATE_CREATED,
       COUNT(t.COUNT)                   as COUNT_SIGN_UPS,
       100 * ( COUNT(*) - LAG(COUNT(*), 1) OVER (order by DATE_CREATED / LAG(COUNT(*), 1) over (order by DATE_CREATED)) || '%') as percent_growth
FROM data t
GROUP BY 1
ORDER BY 1 ASC;

Answer

how about using a sub query:

select DATE_CREATED
      ,COUNT_SIGN_UPS 
      , ((COUNT_SIGN_UPS /LAG(COUNT_SIGN_UPS,1,COUNT_SIGN_UPS) OVER (order by DATE_CREATED) - 1) * 100.0 as percent_growth
from (
   SELECT DATE_TRUNC(WEEK, t.DATE_CREATED) as DATE_CREATED,
          COUNT(t.COUNT)                   as COUNT_SIGN_UPS
   FROM data t
   GROUP BY 1
   ORDER BY 1 ASC;
) t

Advertisement

Answer