Run a query that provides all data entries from the past week in BigQuery

Question

I have a table with data and one of the columns is titled 'createdAt' and is a timestamp. Is there a query that I can run that selects all of the entries that would have been made in the previous week?

This is the code I have so far. I believe it would be implementing a WHERE clause of some kind but I am not sure just how to do it.

#standardSQL
SELECT
  Serial,
  SUM(ConnectionTime/3600) as Total_Hours,
  COUNT(DISTINCT DeviceID) AS Devices_Connected
FROM `dataworks-356fa.FirebaseArchive.testf`
WHERE Model = "BlueBox-pH"
GROUP BY Serial
ORDER BY Serial
LIMIT 1000;

Answer 1

selects all of the entries that would have been made in the previous week?

Below is for BigQuery Standard SQL and restricts data to only previous week and obviously not current week

#standardSQL
SELECT
  Serial,
  SUM(ConnectionTime/3600) AS Total_Hours,
  COUNT(DISTINCT DeviceID) AS Devices_Connected
FROM `dataworks-356fa.FirebaseArchive.testf`, 
  UNNEST([DATE_SUB(CURRENT_DATE(), INTERVAL CAST(FORMAT_DATE('%w', CURRENT_DATE()) AS INT64) DAY)]) AS first_day_of_week 
WHERE Model = 'BlueBox-pH'
AND createdAt 
  BETWEEN DATE_SUB(first_day_of_week, INTERVAL 7 DAY)
  AND DATE_SUB(first_day_of_week, INTERVAL 1 DAY)
GROUP BY Serial
-- ORDER BY Serial
-- LIMIT 1000  

to understand how past week stuff it works - run below

#standardSQL
WITH dates AS (
  SELECT createdAt 
  FROM UNNEST(GENERATE_DATE_ARRAY('2017-01-01', '2017-01-13', INTERVAL 1 DAY)) AS createdAt 
)
SELECT 
  createdAt, 
  FORMAT_DATE('%a', createdAt) AS weekday, 
  FORMAT_DATE('%U', createdAt) AS week_start_Sunday, 
  FORMAT_DATE('%W', createdAt) AS week_start_Monday,
  FORMAT_DATE('%V', createdAt) AS week_start_Monday_prorated,
  DATE_SUB(createdAt, INTERVAL weekday_num DAY) AS first_day_of_week_Sunday,
  DATE_SUB(createdAt, INTERVAL weekday_num - 1 DAY) AS first_day_of_week_Monday,
  DATE_SUB(DATE_SUB(createdAt, INTERVAL weekday_num DAY), INTERVAL 7 DAY) AS first_day_of_prev_week_Sunday,
  DATE_SUB(DATE_SUB(createdAt, INTERVAL weekday_num - 1 DAY), INTERVAL 7 DAY) AS first_day_of_prev_week_Monday
FROM dates, UNNEST([CAST(FORMAT_DATE('%w', createdAt) AS INT64)]) AS weekday_num 
ORDER BY createdAt  

the output is -

createdAt   weekday  week_   week_   week_     first_day_  first_day_  first_day_     first_day_
                     start_  start_  start_    of_week_    of_week_    of_prev_week_  of_prev_week_     
                     Sunday  Monday  Monday_   Sunday      Monday      Sunday         Monday
                                     prorated 
---------------------------------------------------------------------------------------------------
2017-01-01  Sun      01      00      52        2017-01-01  2017-01-02  2016-12-25     2016-12-26     
2017-01-02  Mon      01      01      01        2017-01-01  2017-01-02  2016-12-25     2016-12-26     
2017-01-03  Tue      01      01      01        2017-01-01  2017-01-02  2016-12-25     2016-12-26     
2017-01-04  Wed      01      01      01        2017-01-01  2017-01-02  2016-12-25     2016-12-26     
2017-01-05  Thu      01      01      01        2017-01-01  2017-01-02  2016-12-25     2016-12-26     
2017-01-06  Fri      01      01      01        2017-01-01  2017-01-02  2016-12-25     2016-12-26     
2017-01-07  Sat      01      01      01        2017-01-01  2017-01-02  2016-12-25     2016-12-26     
2017-01-08  Sun      02      01      01        2017-01-08  2017-01-09  2017-01-01     2017-01-02     
2017-01-09  Mon      02      02      02        2017-01-08  2017-01-09  2017-01-01     2017-01-02     
2017-01-10  Tue      02      02      02        2017-01-08  2017-01-09  2017-01-01     2017-01-02     
2017-01-11  Wed      02      02      02        2017-01-08  2017-01-09  2017-01-01     2017-01-02     
2017-01-12  Thu      02      02      02        2017-01-08  2017-01-09  2017-01-01     2017-01-02     
2017-01-13  Fri      02      02      02        2017-01-08  2017-01-09  2017-01-01     2017-01-02     

As you can see, in my answer I am using logic of first_day_of_week_Sunday to calculate first_day_of_week
If you would have same requirements as @Wouter - in my country we consider the Monday to be the first day of the week - you can use logic of first_day_of_week_Monday

Answer 2

You can use DATE_TRUNC with the WEEK part to find the start of the week for a given date. For example,

#standardSQL
WITH Input AS (
  SELECT DATE '2017-06-25' AS date, 1 AS x UNION ALL
  SELECT DATE '2017-06-20', 2 UNION ALL
  SELECT DATE '2017-06-26', 3 UNION ALL
  SELECT DATE '2017-07-11', 4 UNION ALL
  SELECT DATE '2017-07-09', 5
)
SELECT
  DATE_TRUNC(date, WEEK) AS week,
  MAX(x) AS max_x
FROM Input
GROUP BY week;

In your particular case, it would be:

#standardSQL
SELECT
  Serial,
  SUM(ConnectionTime/3600) as Total_Hours,
  COUNT(DISTINCT DeviceID) AS Devices_Connected
FROM `dataworks-356fa.FirebaseArchive.testf`
WHERE Model = "BlueBox-pH" AND
  createdAt >= DATE_TRUNC(CURRENT_DATE(), WEEK)
GROUP BY Serial
ORDER BY Serial
LIMIT 1000;

Alternatively, if you are just looking for dates in the past seven days, you can use a query of this form:

#standardSQL
SELECT
  Serial,
  SUM(ConnectionTime/3600) as Total_Hours,
  COUNT(DISTINCT DeviceID) AS Devices_Connected
FROM `dataworks-356fa.FirebaseArchive.testf`
WHERE Model = "BlueBox-pH" AND
  createdAt >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)
GROUP BY Serial
ORDER BY Serial
LIMIT 1000;

Answer 3

In Standard SQL you can try something like this to see if the WHERE clause gets you the correct date range:

SELECT
  MIN(createdAt),
  MAX(createdAt)
FROM
  `dataworks-356fa.FirebaseArchive.testf`
WHERE
  EXTRACT(WEEK FROM createdAt) = EXTRACT(WEEK FROM CURRENT_TIMESTAMP()) - 1 

Please note that BigQuery uses the Sunday as the first day of the week. I don't know how to change that. Would be interesting if someone knows since in my country we consider the Monday to be the first day of the week.