How can fill the missing dates in google BigQuery

Question

I want to write a chart that shows the active users in firebase

I wrote this code

SELECT event_date, COUNT(DISTINCT user_pseudo_id) AS user_count
FROM `mark-3314e.analytics_197261162.events_*`  
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'session_start'
GROUP BY event_date
ORDER BY event_date ASC

And this is the response

Row event_date  user_count  
1   20190617        1
2   20190621        3

is there any way to fill the missing dates between 21 and 17 with the previous data? like:

event_date  user_count  
20190617        1
20190618        1
20190619        1
20190620        1
20190621        3

Tim Biegeleisen · Answer 1 · 2019-06-22T15:52:36.310

1

You may join with a calendar table which contains the full date range of interest:

WITH dates AS (
    SELECT '20190617' AS dt UNION ALL
    SELECT '20190618' UNION ALL
    SELECT '20190619' UNION ALL
    SELECT '20190620' UNION ALL
    SELECT '20190621'
)

SELECT
    t1.dt AS event_date,
    COUNT(DISTINCT t2.user_pseudo_id) AS user_count
FROM dates t1
LEFT JOIN `mark-3314e.analytics_197261162.events_*` t2
    ON t1.dt = t2.event_date AND
       t2._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
       AND t2.event_name = 'session_start'
GROUP BY
    t1.dt
ORDER BY
    t1.dt;

For a more general way to generate a date range in BigQuery, see this SO question.

edited Jun 22 '19 at 15:52

answered Jun 22 '19 at 15:39

Tim Biegeleisen

502,043
27
286
360

I had to change **t2** in `GROUP BY t2.dt` by **t1** because it doesn't work... then, it still shows me the same result (20190617 1) and (20190621 3) – Aymen Fezai Jun 22 '19 at 15:49
@AymenFezai Try moving all logic in the `WHERE` clause to the `ON` clause of the join. I erred, and should have done this from the start. – Tim Biegeleisen Jun 22 '19 at 15:53
it fill the missing data with zeros but it should fill them with the previous result. I mean, something like this:(20190617 1) , (20190618 1), (20190619 1) and (20190621 3) – Aymen Fezai Jun 22 '19 at 15:57
That requirement did not appear in your original question. – Tim Biegeleisen Jun 22 '19 at 16:01
Yeah, Sorry about – Aymen Fezai Jun 22 '19 at 16:07

score 0 · Answer 2 · answered Jun 23 '19 at 01:55

Here is a possible solution using GENERATE_DATE_ARRAY function in BigQuery.

with data as (
   SELECT parse_date('%Y%m%d', event_date) AS event_date, COUNT(DISTINCT user_pseudo_id) AS user_count
   FROM `mark-3314e.analytics_197261162.events_*`  
   WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
   AND event_name = 'session_start'
   GROUP BY event_date
   ORDER BY event_date ASC
)

select dt as event_date, user_count from (
  select user_count,
      if(
        previousdate is null, 
        generate_date_array(date, date_sub(nextdate, interval 1 day), interval 1 day), 
        generate_date_array(date, if(nextdate is null, date, date_sub(nextdate, interval 1 day)), interval 1 day)
      ) as dates 
  from (
          select 
            lag(event_date) over(order by event_date) as previousdate,
            event_date as date,
            lead(event_date) over(order by event_date) as nextdate,
            user_count
          from data
      )
), unnest(dates) dt

How can fill the missing dates in google BigQuery

2 Answers2