find users who have been watching kid friendly programs continuously for 13 weeks, Sql Server

Language: Layout:

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,'SRT123','US','ipad'),
('2020-02-02',67890,'SRT123','UK','TV'),
('2020-02-03',98765,'SRT123','FR','iPhone'),
('2020-02-04',12345,'SRT123','US','TV'),
('2020-02-05',67890,'SRT123','UK','ipad'),
('2020-02-06',98765,'SRT123','SA','TV'),
('2020-02-07',12345,'SRT123','US','iPhone'),
('2020-02-08',67890,'SRT123','UK','TV'),
('2020-02-09',98765,'SRT123','SO','ipad'),
('2020-02-10',12345,'SRT123','US','TV'),
('2020-02-11',67890,'SRT123','UK','iPhone'),
('2020-02-12',98765,'SRT123','MO','TV'),
('2020-02-13',12345,'SRT123','US','ipad'),
('2020-02-14',67890,'SRT123','UK','TV'),
('2020-02-15',98765,'SRT123','DI','iPhone'),
('2020-02-16',12345,'SRT123','US','TV'),
('2020-02-17',67890,'SRT123','UK','ipad'),
('2020-02-18',98765,'SRT123','MI','TV'),
('2020-02-19',12345,'SRT123','US','iPhone'),
('2020-02-20',67890,'SRT123','UK','TV'),
('2020-02-21',98765,'SRT123','DO','ipad'),
('2020-02-22',12345,'SRT123','US','TV'),
('2020-02-23',67890,'SRT123','UK','iPhone'),
('2020-02-24',98765,'SRT456','FR','TV'),
('2020-02-25',12345,'SRT123','US','ipad'),
('2020-02-26',67890,'SRT123','UK','TV'),
('2020-02-27',98765,'XYZ123','SA','iPhone'),
('2020-02-28',12345,'SRT123','US','TV'),
('2020-02-29',67890,'SRT123','UK','ipad'),
('2020-03-01',98765,'SRT123','SO','TV'),
('2020-03-02',12345,'SRT123','US','iPhone'),
('2020-03-03',67890,'SRT123','UK','TV'),
('2020-03-04',98765,'SRT123','MO','ipad'),
('2020-03-05',12345,'SRT123','US','TV'),
('2020-03-06',67890,'SRT123','UK','iPhone'),
('2020-03-07',98765,'SRT123','DI','TV'),
('2020-03-08',12345,'SRT123','US','ipad'),
('2020-03-09',67890,'SRT123','UK','TV'),
('2020-03-10',98765,'SRT123','MI','iPhone'),
('2020-03-11',12345,'SRT123','US','TV'),
('2020-03-12',67890,'SRT123','UK','ipad'),
('2020-03-13',98765,'SRT123','DO','TV'),
('2020-03-14',12345,'SRT123','US','iPhone'),
('2020-03-15',67890,'SRT123','UK','TV'),
('2020-03-16',98765,'SRT123','FR','ipad'),
('2020-03-17',12345,'SRT123','US','TV'),
('2020-03-18',67890,'SRT123','UK','iPhone'),
('2020-03-19',98765,'SRT123','SA','TV'),
('2020-03-20',12345,'SRT123','US','ipad'),
('2020-03-21',67890,'SRT123','UK','TV'),
('2020-03-22',98765,'SRT123','SO','iPhone'),
('2020-03-23',12345,'SRT123','US','TV'),
('2020-03-24',67890,'SRT123','UK','ipad'),
('2020-03-25',98765,'SRT123','MO','TV'),
('2020-03-26',12345,'SRT123','US','iPhone'),
('2020-03-27',67890,'SRT123','UK','TV'),
('2020-03-28',98765,'SRT123','DI','ipad'),
('2020-03-29',12345,'SRT123','US','TV'),
('2020-03-30',67890,'SRT123','UK','iPhone'),
('2020-03-31',98765,'SRT123','MI','TV'),
('2020-04-01',12345,'SRT123','US','ipad'),
('2020-04-02',67890,'SRT123','UK','TV'),
('2020-04-03',98765,'SRT123','DO','iPhone'),
('2020-04-04',12345,'SRT123','US','TV'),
('2020-04-05',67890,'SRT123','UK','ipad'),
('2020-04-06',98765,'SRT123','FR','TV'),
('2020-04-07',12345,'SRT123','US','iPhone'),
('2020-04-08',67890,'SRT123','UK','TV'),
('2020-04-09',98765,'SRT123','SA','ipad'),
('2020-04-10',12345,'SRT123','US','TV'),
('2020-04-11',67890,'SRT123','UK','iPhone'),
('2020-04-12',98765,'SRT123','SO','TV'),
('2020-04-13',12345,'SRT123','US','ipad'),
('2020-04-14',67890,'SRT123','UK','TV'),
('2020-04-15',98765,'SRT123','MO','iPhone'),
('2020-04-16',12345,'SRT123','US','TV'),
('2020-04-17',67890,'SRT123','UK','ipad'),
('2020-04-18',98765,'SRT123','DI','TV'),
('2020-04-19',12345,'SRT123','US','iPhone'),
('2020-04-20',67890,'SRT123','UK','TV'),
('2020-04-21',98765,'SRT123','MI','ipad'),
('2020-04-22',12345,'SRT123','US','TV'),
('2020-04-23',67890,'SRT123','UK','iPhone'),
('2020-04-24',98765,'SRT123','DO','TV'),
('2020-04-25',12345,'SRT123','US','ipad'),
('2020-04-26',67890,'SRT123','UK','TV'),
('2020-04-27',98765,'SRT123','FR','iPhone'),
('2020-04-28',12345,'SRT123','US','TV'),
('2020-04-29',67890,'SRT123','UK','ipad'),
('2020-04-30',98765,'SRT123','SA','TV'),
('2020-05-01',12345,'SRT123','US','iPhone'),
('2020-05-02',67890,'SRT123','UK','TV'),
('2020-05-03',98765,'SRT123','SO','ipad'),
('2020-05-04',12345,'SRT123','US','TV'),
('2020-05-05',67890,'SRT123','UK','iPhone'),
('2020-05-06',98765,'SRT123','MO','TV'),
('2020-05-07',12345,'SRT123','US','ipad'),
('2020-05-08',67890,'SRT123','UK','TV'),
('2020-05-09',98765,'SRT123','DI','iPhone'),
('2020-05-10',12345,'SRT123','US','TV'),
('2020-05-11',67890,'SRT123','UK','ipad'),
('2020-05-12',98765,'SRT123','MI','TV'),
('2020-05-13',12345,'SRT123','US','iPhone'),
('2020-05-14',67890,'SRT123','UK','TV'),
('2020-05-15',98765,'SRT123','DO','ipad'),
('2020-05-16',12345,'SRT123','US','TV'),
('2020-05-17',67890,'SRT123','UK','iPhone'),
('2020-05-18',98765,'SRT123','FR','TV'),
('2020-05-19',12345,'SRT123','US','ipad'),
('2020-05-20',67890,'SRT123','UK','TV'),
('2020-05-21',98765,'SRT123','SA','iPhone'),
('2020-05-22',12345,'SRT123','US','TV'),
('2020-05-23',67890,'SRT123','UK','ipad'),
('2020-05-24',98765,'SRT123','SO','TV'),
('2020-05-25',12345,'SRT123','US','iPhone'),
('2020-05-26',67890,'SRT123','UK','TV'),
('2020-05-27',98765,'SRT123','MO','ipad'),
('2020-05-28',12345,'SRT123','US','TV'),
('2020-05-29',67890,'SRT123','UK','iPhone');

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())
  group by customer_id, datepart(week,stream_date)
) tmp where kids='y' group by customer_id having count(week)=13

View schema
Execution time: 0,05 sec, rows selected: 2, rows affected: 123, absolute service time: 0,23 sec, absolute service time: 0,23 sec

find users who have been watching kid friendly programs continuously f...