Adding all dates to SQL query for grouped dates

February 22, 2022

I would like SQL to return all dates, whether or not they are null or 0 from the following query:

   SELECT count(id) as Record_Count, delete_date as Date
   FROM table
   WHERE marketing_tag = 'XYZ'
   GROUP BY delete_date
   ORDER BY delete_date desc

Current Output:

| Record_Count  |    Date   |
|100            |   01/07/22|
|200            |   01/05/22|
|250            |   01/02/22|

Desired Output:

|  Record_Count |    Date   |
|100            |   01/07/22|
|0              |   01/06/22|
|200            |   01/05/22|
|0              |   01/04/22|
|0              |   01/03/22|
|250            |   01/02/22|

I do not have another table with dates to reference
My min should be the first date pulled from the query (in this case, 01/02/22) and then my max should be the most recent date that the logic applies to (in this case, 01/07/22) is this possible without the creation of another table?

>Solution :

Consider below approach

select ifnull(record_count, 0) record_count, date
from unnest((
  select generate_date_array(min(delete_date), max(delete_date)) 
  from your_table
)) date 
left join (
  select count(id) as record_count, delete_date as date 
  from your_table 
  where marketing_tag = 'xyz'
  group by delete_date
)
using (date)
order by date desc

if applied to sample data in your question – output is