CODEWITHSUNDEEP
sqlgoogle-bigqueryappend
swm
swm

Reputation: 539

Appending the result query in bigquery

I am doing a query where the query will append the data from previous date as the outcome in BigQuery. So, the result data for today will be higher than yesterdays as the data is appending by days. So far, what I only managed to get the outcome is the data by days (where you can see the number of ID declining and is not appending from previous day) as this result:

enter image description here

What should I do to add appending function in the query so each day will get the result of data from the previous day in bigquery?

code:
    WITH
    table1 AS (
    SELECT
         ID, 
      ...
    
       FROM t
       WHERE DATE_SUB('2020-01-31', INTERVAL 31 DAY) and '2020-01-31'
    ),
    
    table2 AS (
    SELECT 
        ID,
        COUNTIF((rating  < 7) as bad,
        COUNTIF((rating  >= 7 AND SAFE_CAST(NPS_Rating as INT64) < 9) as intermediate,
        COUNTIF((rating  as good
         
    FROM
        t
    WHERE DATE_SUB('2020-01-31', INTERVAL 31 DAY) and '2020-01-31'
    )
    
    SELECT  
    DATE_SUB('2020-01-31', INTERVAL 31 DAY) as date,
    * 
    FROM table1 
    FULL OUTER JOIN table2 USING (ID)

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you have counts that you want to accumulate, then you want a cumulative sum. The query would look something like this:

select datecol, count(*), sum(count(*)) over (order by datecol)
from t
group by datecol
order by datecol;

Upvotes: 1

Related Questions