/*-- Query 7:

--From the weather table, fetch all the records when London had extremely cold temperature for 3 consecutive days or more.

--Note: Weather is considered to be extremely cold then its temperature is less than zero.

--Table Structure:

create table weather
(
id int,
city varchar(50),
temperature int,
day date
);

insert into weather values
(1, 'London', -1, '2021-01-01'),
(2, 'London', -2, '2021-01-02'),
(3, 'London', 4, '2021-01-03'),
(4, 'London', 1, '2021-01-04'),
(5, 'London', -2, '2021-01-05'),
(6, 'London', -5, '2021-01-06'),
(7, 'London', -7, '2021-01-07'),
(8, 'London', 5, '2021-01-08');

select id,city, temperature, day
from(
select *,
case when temperature<0 
    and lead(temperature) over(order by day) <0
    and lead(temperature,2) over(order by day)<0 
    then 'yes'
    when temperature<0 
    and lag(temperature) over(order by day) <0
    and lead(temperature) over(order by day)<0 
    then 'yes'
    when temperature<0 
    and lag(temperature) over(order by day) <0
    and lag(temperature,2) over(order by day)<0 
    then 'yes'
end as flag
from weather)x
where x.flag='yes'
*/

-- Query 8:

--From the following 3 tables (event_category, physician_speciality, patient_treatment),
--write a SQL query to get the histogram of specialities of the unique physicians
--who have done the procedures but never did prescribe anything.

--Table Structure:

create table event_category
(
  event_name varchar(50),
  category varchar(100)
);


create table physician_speciality
(
  physician_id int,
  speciality varchar(50)
);


create table patient_treatment
(
  patient_id int,
  event_name varchar(50),
  physician_id int
);


insert into event_category values ('Chemotherapy','Procedure');
insert into event_category values ('Radiation','Procedure');
insert into event_category values ('Immunosuppressants','Prescription');
insert into event_category values ('BTKI','Prescription');
insert into event_category values ('Biopsy','Test');


insert into physician_speciality values (1000,'Radiologist');
insert into physician_speciality values (2000,'Oncologist');
insert into physician_speciality values (3000,'Hermatologist');
insert into physician_speciality values (4000,'Oncologist');
insert into physician_speciality values (5000,'Pathologist');
insert into physician_speciality values (6000,'Oncologist');


insert into patient_treatment values (1,'Radiation', 1000);
insert into patient_treatment values (2,'Chemotherapy', 2000);
insert into patient_treatment values (1,'Biopsy', 1000);
insert into patient_treatment values (3,'Immunosuppressants', 2000);
insert into patient_treatment values (4,'BTKI', 3000);
insert into patient_treatment values (5,'Radiation', 4000);
insert into patient_treatment values (4,'Chemotherapy', 2000);
insert into patient_treatment values (1,'Biopsy', 5000);
insert into patient_treatment values (6,'Chemotherapy', 6000);

select ps.speciality, count(*)
from patient_treatment pt
join event_category e on pt.event_name = e.event_name
join physician_speciality ps on ps.physician_id = pt.physician_id
where e.category ='Procedure'and pt.physician_id not in (select pt2.physician_id
                                            from patient_treatment pt2
                                            join event_category e2
                                            on pt2.event_name = e2.event_name
                                            where e2.category ='Prescription')
group by ps.speciality