CODEWITHSUNDEEP
sqlgoogle-bigquery
Ashwin Kumar
Ashwin Kumar

Reputation: 11

Issue in big query. how can I calculate percentage?

SELECT count(time_stamp) AS Cnt,
       (CASE
            WHEN DATEDIFF(CURRENT_DATE(), time_stamp) < 30 THEN "This_month"
            WHEN DATEDIFF(CURRENT_DATE(), time_stamp) >= 30
                 AND DATEDIFF(CURRENT_DATE(), time_stamp) <= 60 THEN "Past_month"
            ELSE "rest"
        END) AS dateblock
FROM database90days
GROUP BY dateblock

now I get the result like

Cnt      dateblock  
964      rest
1,035    Past_month
1,176    This_month

now I need to check the percentage increase and percentage decrease to thismonth to last month. Please help me how to do in the above query

Upvotes: 1

Views: 904

Answers (2)

Philipp Sh
Philipp Sh

Reputation: 997

I am not sure that this solution is what you are looking for exactly, but I believe it is a workaround if you are planning to use the 3 value structure (this month, past month, rest): 

SELECT 100*((This_month-Past_month)/Past_month) as change_ratio_in_percent,
      This_month,
      Past_month,
      BEginning
      FROM (
       SELECT (select count(*) FROM dataset WHERE DATE_DIFF(CURRENT_DATE(), time_stamp, DAY)< 30) AS This_month,
       (select count(*) FROM dataset WHERE DATE_DIFF(CURRENT_DATE(), time_stamp, DAY) >= 30 AND DATE_DIFF(CURRENT_DATE(), first_name, DAY) <= 60) AS Past_month,
       (select count(*) FROM dataset WHERE DATE_DIFF(CURRENT_DATE(), time_stamp, DAY) > 60) AS BEginning
FROM dataset)
GROUP BY change_ratio_in_percent, This_month, Past_month, BEginning;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269723

You can use LAG() to get the previous count. 

SELECT datablock,
       (CASE WHEN DATEDIFF(CURRENT_DATE(), time_stamp) < 30 THEN 'This_month'
             WHEN DATEDIFF(CURRENT_DATE(), time_stamp) >= 30 AND
                  DATEDIFF(CURRENT_DATE(), time_stamp) <= 60 THEN 'Past_month'
             ELSE 'rest'
        END) AS dateblock
       COUNT(time_stamp) AS Cnt,
       LAG(COUNT(time_stamp)) OVER (PARTITION BY datablock ORDER BY MIN(time_stamp)) as prev_cnt
FROM database90days
GROUP BY dateblock;

The rest is just arithmetic on these values.

Upvotes: 2

Related Questions