Expand a query from a date to a range of dates

Question

I have a query as below:

SELECT
  "2022-05-10 00:00:00 UTC" AS date_,
  COUNT(salesId) AS total-sales
FROM
  `project1.sales.sales-growth`
WHERE
  (promoDate BETWEEN "2022-05-10 00:00:00 UTC"
  AND "2022-05-11 00:00:00 UTC")
  OR
  (purchaseDate BETWEEN "2022-05-10 00:00:00 UTC"
  AND "2022-05-11 00:00:00 UTC")

Which shows the total sale for a particular date (2022-05-11) as below:

date_         total-sales
2022-05-10    560

I am wondering how I can change the query to show all the May month sales per day (desired output):

date_         total-sales
2022-05-01    567
2022-05-02    687
2022-05-03    878
...           ...
2022-05-31    500

Try using `group by YEAR(promoDate),MONTH(promoDate),DAY(promoDate)` — Ergest Basha, Jun 22 '22 at 10:16
Check https://stackoverflow.com/questions/38694040/how-to-generate-date-series-to-occupy-absent-dates-in-google-biqquery — Ergest Basha, Jun 22 '22 at 10:53

score 1 · Answer 1 · answered Jun 22 '22 at 11:18

One option: generate a date array for the target time range, group by those dates and compare those dates in the WHERE clause with your two date columns.

With an assumed table of yours:

WITH your_table AS 
(
  SELECT TIMESTAMP("2022-05-01 15:30:00+00") AS promoDate, NULL AS purchaseDate, 1 AS salesId
  UNION ALL
  SELECT NULL AS promoDate, TIMESTAMP("2022-05-01 18:30:00+00") AS purchaseDate, 1 AS salesId
  UNION ALL
  SELECT TIMESTAMP("2022-05-02 15:30:00+00") AS promoDate, NULL AS purchaseDate, 1 AS salesId
  UNION ALL
  SELECT TIMESTAMP("2022-05-03 15:30:00+00") AS promoDate, NULL AS purchaseDate, 1 AS salesId
  UNION ALL
  SELECT TIMESTAMP("2022-05-04 15:30:00+00") AS promoDate, NULL AS purchaseDate, 1 AS salesId
  UNION ALL
  SELECT NULL AS promoDate, TIMESTAMP("2022-05-04 18:30:00+00") AS purchaseDate, 1 AS salesId
)

SELECT
  date_,
  COUNT(salesId) AS total_sales
FROM
  UNNEST(GENERATE_DATE_ARRAY("2022-05-01", "2022-05-31")) AS date_, your_table 
WHERE
  date_ = EXTRACT(DATE FROM promoDate)
  OR
  date_ = EXTRACT(DATE FROM purchaseDate)
GROUP BY 
  date_

Output:

Row	date_	total_sales
1	2022-05-01	2
2	2022-05-02	1
3	2022-05-03	1
4	2022-05-04	2

Expand a query from a date to a range of dates

1 Answers1