Trend Cards for when there's empty data. SQL Query editing

Get Help
1

I have this query that queries 2 records and 2 fields. The first field gives me the filtered date; the second field gives me the count of something.

For the 23rd of April 2023, there are 0 records and hence I don't see any values for it. I only see a record for the 22nd of April 2023.

Since I am using a trend card, I need the 23rd date with 0 as the count so I can compare. How do I do this?

SELECT
    DATE(date) AS date_, 
    COUNT(DISTINCT id)
FROM abc.fe
JOIN abc.bcnm
USING(xx)
WHERE
    (DATE(date) = {{date_filter}} OR 
    DATE(date) = {{date_filter}}::date - INTERVAL '1 DAY')
    AND {{xx}}
    AND {{yy}}
GROUP BY date_
ORDER BY date_ ASC
2

Create the record in the table to return a zero for a row, or build it with a formula in a cte so it returns a zero

3

This issue has now been solved with this query:

WITH temp AS
(
SELECT
    DATE(date) AS date_, 
    COUNT(DISTINCT ID) AS count_
FROM abc.def
WHERE
    (DATE(date) = {{date_filter}} OR 
    DATE(date) = {{date_filter}}::date - INTERVAL '1 DAY')
GROUP BY date_
)

SELECT 
    temp_.date_,
    COALESCE(count_, 0)
FROM temp
RIGHT JOIN
(
SELECT {{date_filter}} ::date AS date_
UNION
SELECT {{date_filter}} ::date - INTERVAL '1 DAY'
) AS temp_
ON temp.date_ = temp_.date_
ORDER BY temp_.date_ ASC