skip to Main Content

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

enter image description here

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


  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.
    
    Login or Signup to reply.
  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;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search