I am trying to calculate the percent growth of a single column (COUNT column) as shown below:
x
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;
Advertisement
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