Distinct count with dynamic start date and end date

I have this dataset

Webinar:

id    title     start              end
1     A         2020-01-01 10:00   2020-01-01 12:00
2     B         2020-01-02 15:00   2020-01-02 17:30

User_activity

id    user_id    webinar_id   created_at
1     1          1            2020-01-01 10:01
2     2          1            2020-01-01 12:01
3     1          2            2020-01-02 15:01

I want to know the duration of each webinar in minutes and number of unique user that view webinar in start time and end time of each webinar

for example:

id    title     start              end                duration   view
1     A         2020-01-01 10:00   2020-01-01 12:00   120        1
2     B         2020-01-02 15:00   2020-01-02 17:30   150        1

User 2 is not counted because he is exceed the end time

Answer

You can try the below –

select a.id,title,start,end,timestampdiff(MINUTE,end,start) as duration,
       count(distinct user_id) as view
from Webinar a join User_activity u on a.id=u.webinar_id
where created_at>=start and created_at<=end
group by a.id,title,start,end

Advertisement

Answer