Bigquery Week level info

0
votes

I have a query that returns daily data for the last 7 days. I would like to know the syntax for getting weekly data for the last 4 weeks using bigquery

-Week Total

week 1 15 week 2 20 week 3 35

1
google-bigquery

1 Answers

1
votes

Something along those lines:

SELECT 
  YEAR(day) AS year, 
  WEEK(day) AS week, 
  SUM(metric) AS total 
FROM YourTable
WHERE WEEK(CURRENT_DATE()) - WEEK(day) < 4
GROUP BY 1, 2

To test/play - you can use below approach that hopefuly mimics your data

SELECT 
  YEAR(day) AS year, 
  WEEK(day) AS week, 
  SUM(metric) AS total 
FROM (
  SELECT 
    DATE(DATE_ADD(TIMESTAMP('2016-01-01'), pos - 1, "DAY")) AS day, 
    CAST(100 * RAND() AS INTEGER) AS metric
  FROM (
       SELECT ROW_NUMBER() OVER() AS pos, *
       FROM (FLATTEN((
       SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP('2016-01-01')), '.'),'') AS h
       FROM (SELECT NULL)),h
  )))
) AS YourTable
WHERE WEEK(CURRENT_DATE()) - WEEK(day) < 4
GROUP BY 1, 2