view raw
Ravi Ravi - 11 months ago 60
SQL Question

Complex SQL queries

I have a table called

Subscriptions (user_name varchar(50) , video_name varchar(50), watched_date date)

ravi   simsons      2016-01-01

ravi   dailyshow     2016-02-15

nitin   dailyshow     2016-02-24

nitin   simsons      2016-02-25

sam   simsons      2016-03-04

sam   dailyshow     2016-03-04

mat    simsons      2016-04-06

cranw  simsons      2016-04-15

cranw   dailyshow     2016-04-17

simsons and dailyshow are the names of the video

I need to find the distinct count of user_names who watched Simsons before dailyshow.

I'm struggling to do with a self join and case statement, but still cannot get the correct query.

Please help !!!

vkp vkp
select count(distinct sdaily.user_name)
from (select * from subscriptions where video_name = 'simsons') ssim
join (select * from subscriptions where video_name = 'dailyshow') sdaily
on sdaily.user_name = ssim.user_name
where ssim.watched_date < sdaily.watched_date