I have the following code which gets me how many rows were written on each day there was anything done.

SELECT
  ingestion_time,
  COUNT(ingestion_time) AS Rows_Written,
FROM
  `workday.ingestions`
GROUP BY
  ingestion_time
ORDER BY
  ingestion_time

Which will give me something that looks like the following:

However, I want to be able to add in the missing dates so the tables looks like this instead:

How can I go about doing this? Do need to create a whole table with all dates and join it somehow, or is there another way? Thanks in advance.

CodePudding user response：

Consider below approach

select date(Ingestion_Time) Ingestion_Time, Rows_Written 
from your_current_query union all
select day, 0 from (
  select *, lead(Ingestion_Time) over(order by Ingestion_Time) next_time 
  from your_current_query
), unnest(generate_date_array(date(Ingestion_Time)   1, date(next_time) - 1)) day            

if to apply to sample data in your question - output is