CODEWITHSUNDEEP
sql
OctavianWR
OctavianWR

Reputation: 227

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

Upvotes: 0

Views: 133

Answers (2)

Fahmi
Fahmi

Reputation: 37483

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

Upvotes: 2

id&#39;7238
id&#39;7238

Reputation: 2611

Use the TIMESTAMDIFF function if the datatype is datetime:

SELECT w.*, TIMESTAMPDIFF(MINUTE, w.start, w.end) AS duration, 
    ( SELECT COUNT(DISTINCT user_id) FROM User_activity ua 
      WHERE ua.webinar_id = w.id 
      AND ua.created_at BETWEEN w.start and w.end
    ) AS `view`
FROM Webinar w

Updated: COUNT function with DISTINCT instead COUNT(*) is used to count unique users.

Upvotes: 2

Related Questions