Find users who have been watching kid's programmes for 13 weeks, Sql Server

Find users who have been watching kid's programmes for 13 weeks

create table content (Content_ID char(8), KIDS_CONTENT char(1), Content_type char(8));
create table streams (Stream_date date, Customer_id int, Content_ID char(8), Marketplace_ID char(3), device_type char(8))
insert into content (Content_ID, KIDS_CONTENT, Content_type) VALUES
('SRT123','Y','series'),
('SRT456','N','series'),
('XYZ123','N','movie'),
('ABC123','Y','movie');
insert into streams (Stream_date, Customer_id, Content_ID, Marketplace_ID, device_type) VALUES
('2020-02-01',12345,'ABC123','US','ipad'),
('2020-02-02',67890,'SRT123','UK','TV'),
('2020-02-03',98765,'ABC123','FR','iPhone'),
('2020-02-04',12345,'ABC123','US','TV'),
('2020-02-05',67890,'SRT123','UK','ipad'),
('2020-02-06',98765,'ABC123','SA','TV'),
('2020-02-07',12345,'ABC123','US','iPhone'),
('2020-02-08',67890,'SRT123','UK','TV'),
('2020-02-09',98765,'ABC123','SO','ipad'),
('2020-02-10',12345,'ABC123','US','TV'),
('2020-02-11',67890,'SRT123','UK','iPhone'),
('2020-02-12',98765,'ABC123','MO','TV'),
('2020-02-13',12345,'ABC123','US','ipad'),
('2020-02-14',67890,'SRT123','UK','TV'),
('2020-02-15',98765,'ABC123','DI','iPhone'),
('2020-02-16',12345,'ABC123','US','TV'),
('2020-02-17',67890,'SRT123','UK','ipad'),
('2020-02-18',98765,'ABC123','MI','TV'),
('2020-02-19',12345,'ABC123','US','iPhone'),
('2020-02-20',67890,'SRT123','UK','TV'),
('2020-02-21',98765,'ABC123','DO','ipad'),
('2020-02-22',12345,'ABC123','US','TV'),
('2020-02-23',67890,'SRT123','UK','iPhone'),
('2020-02-24',98765,'ABC456','FR','TV'),
('2020-02-25',12345,'ABC123','US','ipad'),
('2020-02-26',67890,'SRT123','UK','TV'),
('2020-02-27',98765,'XYZ123','SA','iPhone'),
('2020-02-28',12345,'ABC123','US','TV'),
('2020-02-29',67890,'SRT123','UK','ipad'),
('2020-03-01',98765,'ABC123','SO','TV'),
('2020-03-02',12345,'ABC123','US','iPhone'),
('2020-03-03',67890,'SRT123','UK','TV'),
('2020-03-04',98765,'ABC123','MO','ipad'),
('2020-03-05',12345,'ABC123','US','TV'),
('2020-03-06',67890,'SRT123','UK','iPhone'),
('2020-03-07',98765,'ABC123','DI','TV'),
('2020-03-08',12345,'ABC123','US','ipad'),
('2020-03-09',67890,'SRT123','UK','TV'),
('2020-03-10',98765,'ABC123','MI','iPhone'),
('2020-03-11',12345,'ABC123','US','TV'),
('2020-03-12',67890,'SRT123','UK','ipad'),
('2020-03-13',98765,'ABC123','DO','TV'),
('2020-03-14',12345,'ABC123','US','iPhone'),
('2020-03-15',67890,'SRT123','UK','TV'),
('2020-03-16',98765,'ABC123','FR','ipad'),
('2020-03-17',12345,'ABC123','US','TV'),
('2020-03-18',67890,'SRT123','UK','iPhone'),
('2020-03-19',98765,'ABC123','SA','TV'),
('2020-03-20',12345,'ABC123','US','ipad'),
('2020-03-21',67890,'SRT123','UK','TV'),
('2020-03-22',98765,'ABC123','SO','iPhone'),
('2020-03-23',12345,'ABC123','US','TV'),
('2020-03-24',67890,'SRT123','UK','ipad'),
('2020-03-25',98765,'ABC123','MO','TV'),
('2020-03-26',12345,'ABC123','US','iPhone'),
('2020-03-27',67890,'SRT123','UK','TV'),
('2020-03-28',98765,'ABC123','DI','ipad'),
('2020-03-29',12345,'ABC123','US','TV'),
('2020-03-30',67890,'SRT123','UK','iPhone'),
('2020-03-31',98765,'ABC123','MI','TV'),
('2020-04-01',12345,'ABC123','US','ipad'),
('2020-04-02',67890,'SRT123','UK','TV'),
('2020-04-03',98765,'ABC123','DO','iPhone'),
('2020-04-04',12345,'ABC123','US','TV'),
('2020-04-05',67890,'SRT123','UK','ipad'),
('2020-04-06',98765,'ABC123','FR','TV'),
('2020-04-07',12345,'ABC123','US','iPhone'),
('2020-04-08',67890,'SRT123','UK','TV'),
('2020-04-09',98765,'ABC123','SA','ipad'),
('2020-04-10',12345,'ABC123','US','TV'),
('2020-04-11',67890,'SRT123','UK','iPhone'),
('2020-04-12',98765,'ABC123','SO','TV'),
('2020-04-13',12345,'ABC123','US','ipad'),
('2020-04-14',67890,'SRT123','UK','TV'),
('2020-04-15',98765,'ABC123','MO','iPhone'),
('2020-04-16',12345,'ABC123','US','TV'),
('2020-04-17',67890,'SRT123','UK','ipad'),
('2020-04-18',98765,'ABC123','DI','TV'),
('2020-04-19',12345,'ABC123','US','iPhone'),
('2020-04-20',67890,'SRT123','UK','TV'),
('2020-04-21',98765,'ABC123','MI','ipad'),
('2020-04-22',12345,'ABC123','US','TV'),
('2020-04-23',67890,'SRT123','UK','iPhone'),
('2020-04-24',98765,'ABC123','DO','TV'),
('2020-04-25',12345,'ABC123','US','ipad'),
('2020-04-26',67890,'SRT123','UK','TV'),
('2020-04-27',98765,'ABC123','FR','iPhone'),
('2020-04-28',12345,'ABC123','US','TV'),
('2020-04-29',67890,'SRT123','UK','ipad'),
('2020-04-30',98765,'ABC123','SA','TV'),
('2020-05-01',12345,'ABC123','US','iPhone'),
('2020-05-02',67890,'SRT123','UK','TV'),
('2020-05-03',98765,'ABC123','SO','ipad'),
('2020-05-04',12345,'ABC123','US','TV'),
('2020-05-05',67890,'SRT123','UK','iPhone'),
('2020-05-06',98765,'ABC123','MO','TV'),
('2020-05-07',12345,'ABC123','US','ipad'),
('2020-05-08',67890,'SRT123','UK','TV'),
('2020-05-09',98765,'ABC123','DI','iPhone'),
('2020-05-10',12345,'ABC123','US','TV'),
('2020-05-11',67890,'SRT123','UK','ipad'),
('2020-05-12',98765,'ABC123','MI','TV'),
('2020-05-13',12345,'ABC123','US','iPhone'),
('2020-05-14',67890,'SRT123','UK','TV'),
('2020-05-15',98765,'ABC123','DO','ipad'),
('2020-05-16',12345,'ABC123','US','TV'),
('2020-05-17',67890,'SRT123','UK','iPhone'),
('2020-05-18',98765,'ABC123','FR','TV'),
('2020-05-19',12345,'ABC123','US','ipad'),
('2020-05-20',67890,'SRT123','UK','TV'),
('2020-05-21',98765,'ABC123','SA','iPhone'),
('2020-05-22',12345,'ABC123','US','TV'),
('2020-05-23',67890,'SRT123','UK','ipad'),
('2020-05-24',98765,'ABC123','SO','TV'),
('2020-05-25',12345,'ABC123','US','iPhone'),
('2020-05-26',67890,'SRT123','UK','TV'),
('2020-05-27',98765,'ABC123','MO','ipad'),
('2020-05-28',12345,'ABC123','US','TV'),
('2020-05-29',67890,'SRT123','UK','iPhone');

/* The query will find all users who have watched at least one kid friendly program in the
   last 13 weeks (counting backwards from the most recent Saturday).
   (( The original task was to look over the "last 3 months" but that is a rather unspecific 
      requirement as months can be longer or shorter and can also start in the middle of a week.
      I replaced the time span by "13 weeks" as this is always a well defined interval. ))             */

select customer_id, count(week) cnt FROM (
  select customer_id, datepart(week,stream_date) week, max(c.kids_content) kids
  from streams s inner join content c on c.content_id=s.content_id
  where stream_date between getdate()-datepart(weekday,getdate())-13*7+1 and getdate()-datepart(weekday,getdate())  -- last 13 weeks
    and content_type='movie'                                                                                        -- only movies count
  group by customer_id, datepart(week,stream_date)
) tmp where kids='y' group by customer_id having count(week)=13                                                     -- each week -> =13

/* The condition "max(c.kids_content)" will always pick out the 'Y' from any collection of results, so 
   if at least one kid friendly programme was watched it will be registered.
   
   So, user 12345 is the only one fulfilling the requirements, 67890 was watching a kid friendly series
   instead of movies and 98765 ALMOST managed it but had a single week without a kid's movie.
   The rules are very strict ... ;-)


run \| edit \| history \| help	0