Basic left join is not working on PostgresSQL

Question

I have two tables which I want to left join. One with dates ranging between 2 Augustus 2023 - 10 augustus 2023. I want to join this with my ad interaction table. The Ad interaction table only has interactions from 8,9 and 10 August. I wanted the final table to show the number of ad interactions for each date, if there are none it should show 0. However, my code now just skips the date and does not show 0. I don't understand why, since I am doing a LEFT JOIN.

I used this link to check if the join is correct.

select dt::date, ads.interaction.created_at::date as ad_interaction_date, count(*) as interactions 
from 
    generate_series('2023-08-02', '2023-08-10', '1 day'::interval) as dt
    left join ads.interaction 
    on dt::date = ads.interaction.created_at::date
where ad_id = 'bdc9dd4d-3d50-4813-89c1-08939eca5709'
group by dt::date, ads.interaction.created_at::date

So, to clarify, the table now is not showing rows with 'dt' = 2023-08-02 up to 2023-08-07. I used the generate_series based on this question.

Answer 1

    SELECT 
        dt::date, 
        COALESCE(ads.interaction.created_at::date, dt::date) as ad_interaction_date, 
        COUNT(ads.interaction.created_at) as interactions 
    FROM 
        generate_series('2023-08-02', '2023-08-10', '1 day'::interval) as dt
    LEFT JOIN ads.interaction 
        ON dt::date = ads.interaction.created_at::date
        AND ad_id = 'bdc9dd4d-3d50-4813-89c1-08939eca5709'
    GROUP BY dt::date, ad_interaction_date
    ORDER BY dt::date;
    
   COALESCE was used to handle the case of no interactions on a particular date. This will display the date itself even if there is no interaction.

Answer 2

you can try this

SELECT dt::date, ads.interaction.created_at::date as ad_interaction_date, 
count(ads.interaction.created_at) as interactions 
FROM generate_series('2023-08-02', '2023-08-10', '1 day'::interval) as dt
LEFT JOIN ads.interaction 
ON dt::date = ads.interaction.created_at::date AND ads.interaction.ad_id = 
'bdc9dd4d-3d50-4813-89c1-08939eca5709'
GROUP BY dt::date, ads.interaction.created_at::date
 ORDER BY dt::date;