My intention is for this query to return dates even if it is empty.
select FROM_DAYS(TO_DAYS(date)) as dt, count(*) as no FROM days
left join tcgaz_data on date = DATE_FORMAT(date_resolved, '%Y-%m-%d')
WHERE date >= CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m-%d')) - INTERVAL 20 DAY
and product_platform in ('0', 'CSP')
group by FROM_DAYS(TO_DAYS(date))
order by dt
It is skipping the dates from the days table where there is not a value in the data table.
since you write columns in your SQL without qualifying which table they came from (not best practice), it's hard to figure out what you're doing, and we have to guess
for instance, i'll bet the problem is due to the fact that product_platform is actually in the tcgaz_data table, which would explain why the LEFT OUTER JOIN isn't working
here's my guess at a fix:
SELECT days.date as dt
, COUNT(tcgaz_data.date_resolved) as no
ON tcgaz_data.date_resolved >= days.date
AND tcgaz_data.date_resolved < days.date + INTERVAL 1 DAY
AND tcgaz_data.product_platform in ('0', 'CSP')
WHERE days.date >= CURRENT_DATE - INTERVAL 20 DAY
Or reverse those as (product_platform,date_resolved) should allow you to match better on the index. Another item that we have done in the past is to create a user function that creates a table of all dates within a range of dates. This allows you to equate to the date, rather than using greater than/less than.