In Bigquery - How to query Yesterday data & Last 7 days data from Firebase event table

2
votes

I use below query to fetch data for specified data range. 

SELECT event_date, count(event_name) as APP_Installs FROM 
`<Table>.events_*`  WHERE _TABLE_SUFFIX BETWEEN '201900201'  AND '20190228' 
and event_name='first_open' group by 1
  • How to query event table for yesterday data without mentioned the date value in Bigquery?
  • How to query event table for last 7 days data without mentioned the date value in Bigquery?

Pls. Help

1
google-bigquery
use current date and other date function to create last day and last 7 day – Vishal Gupta
I know it's an old question, but just to point out, when you typed those dates in by hand, you added an extra 0... '201900201' should have been '20190201' – MatBailie

1 Answers

6
votes

Date functions are what you need here.

To look at the last 7 days, use the following query:

SELECT event_date, count(event_name) as APP_Installs 
FROM `<Table>.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 = 'first_open'
GROUP BY 1

To look at yesterday's data, use the following query:

SELECT event_date, count(event_name) as APP_Installs 
FROM `<Table>.events_*`  
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND event_name = 'first_open'
GROUP BY 1