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

Question

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

Answer 1

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