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.
2
Answers
you can try this