CODEWITHSUNDEEP
sqlgoogle-bigquery
Sana
Sana

Reputation: 563

Expand a query from a date to a range of dates

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

Upvotes: 0

Views: 49

Answers (1)

dnnshssm
dnnshssm

Reputation: 1305

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

Upvotes: 1

Related Questions