A streaming company needs to measure the AVERAGE time spread for users who watch

Question

A streaming company needs to measure the AVERAGE time spread for users who watch video on their platform in a session. Each user has one time spread, which is the difference between her longest session streaming video and her shortest session in her individual history with the company (regardless of the users’ age at the time the sessions were made). The company is interested in users between the ages of 26 and 31, inclusive, who have at least 50 sessions stored in the DB. There are several tables in the DB, but the ones of interested here are:

  User (uid__, name, age) , SessionRecord(uid__, date__, duration)

Primary key attributes are underlined. In the table definitions, the attribute of age is declared as NOT NULL, but duration can be NULL. Write a query that satisfies the above requirements by making sure to use GROUP BY and HAVING clauses.

Explanation / Answer

select avg(timeSpread) from (select uxID,max(dur)- min(dur) as timeSpread
from
( select s.userid as uxID,s.duration as dur ,s.WATCHDATE,u.name as uName,u.age
from SessionRecord s join Users u on s.userid =u.userid  
where s.duration is not null and u.age between 26 and 31) group by uxID having count(dur) >=50 );

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.