Google BigQuery query to generate each day between startDate and endDate. From that count the rows if no rows count as 0

Question

I have a data with name(String), createdAt(Timestamp).

I have 2 input which is startDate(2021-01-01) and endDate(2021-01-04)

Date | Count(*)

2021-01-01 | 1
2021-01-02 | 0
2021-01-03 | 2
2021-01-04 | 4

What I am doing is that I use some a query like this to get all the count

SELECT DATE(createdAT), count(*)
FROM  myProject
WHERE DATE(createdAT) <= endDate AND DATE(createdAT) >= startDate
GROUP BY DATE(createdAT)

This will give back.

2021-01-01 | 1
2021-01-03 | 2
2021-01-04 | 4

And then I process to add "2021-01-02 | 0" in the backend code

2021-01-01 | 1
2021-01-02 | 0
2021-01-03 | 2
2021-01-04 | 4

Is there a query that can handle this logic without doing it in the backend

Thanks you

Answer 1

Yes, you can use the generate_date_array function which returns an array of all the dates between your start_date and end_date.

In your study, you can do everything with the unique following query:

SELECT
  dates,
  COUNT(createdAt) AS event_count
FROM
  UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) dates
LEFT JOIN
  `project.dataset.table` s
ON
  dates = DATE(s.createdAt)
GROUP BY
  dates