CODEWITHSUNDEEP
sqlgoogle-bigquerylegacy-sql
unnest_me
unnest_me

Reputation: 215

How to add in missing dates as rows in table

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:

Ingestion_Time Rows_Written
Jan 2, 2021 8
Jan 5, 2021 5
Jan 8, 2021 9
Jan 9, 2021 2

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

Ingestion_Time Rows_Written
Jan 2, 2021 8
Jan 3, 2021 0
Jan 4, 2021 0
Jan 5, 2021 5
Jan 6, 2021 0
Jan 7, 2021 0
Jan 8, 2021 9
Jan 9, 2021 2

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.

Upvotes: 0

Views: 922

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

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

enter image description here

Upvotes: 1

Related Questions