--- subquery using from

select * from (
  select * from patients
  where date_of_brith >= '2009-01-01'
  order by master_patient_id
) p where p.name ilike 'm%';

--- subquery using joins

select * from (
  select * from surgical_encounters
  where surgical_admission_date 
  between '2016-11-01' and '2016-11-30') se 
  inner joins(
  select master_patient_id from patients
  where date_of_brith >= '1998-01-01'
  ) p on se.master_patient_id = p.master_patient_id
  
  --- CTE, uses with clause
  
  with young_patients as (
    select * from patients
    where date_of_brith >= '2009-01-01'
  )
  
  select * from young_patients
  where name ilike 'm%'
  
--- complex CTE example
--- getting number of surgeries by county where count of patients > 1500
---  using subquery in FROM and JOIN

with top_counties as (
  select county, count(*) as num_patients
  from patients
  group by county
  having count(*) > 1500 ),
county_patients as (
  select p.master_patient_id, p.county
  from patients 
  inner join top_counties t on p.county = t.county
)

select p.county, count(s.surgery_id) 
from surgical_encounters s
inner join county_patients p on s.master_patient_id=p.master_patient_id
group by p.county;

--- subquery for comparison 
--- comparison using subquery in WHERE and HAVING
with total_cost as (
select surgery_id, sum(resource_cost) as total_surgery_cost
from surgical_costs
group by surgery_id;
) 
select *  from total_cost
where total_surgery_cost > (
  select avg(total_surgery_cost)
  from total_cost
);

select * from vitals
where bp_diastolic > (select min(bp_diastolic) > vitals)
and bp_systolic < (select max(bp_systolic) from vitals);


--- using IN and NOT IN for comparison in CTE, to compare lists. Often wrttien as joins depending on performance.

select * from patients
where master_patient_id not in (
  select distinct master_patient_id from surgical_encounters)
order by master_patient_id;

select distinct p.master_patient_id
from patients p
inner join surgical_encounters s on p.master_patient_id = s.master_patient_id
order by p.master_patient_id;

--- ANY or ALL also used for CTE comparison. Should have some type of comparator preceeding it
--- subquery for ANY should return one column , then it checks on it.

--- list of surgeries where total profit is > avg cost of all diagnosis type
select * from surgical_encounters
where total_profil > all (
  select avg(total_cost)
  from surgical_encounters
  group by diagnosis_description
);

--- diagnosis where avg. length of stay is <= avg lebgth of stay for all diagnosis in the department
select diagnosis_description,
avg(surgical_discharge_date - surgical_admission_date)
as length_of_stay
from surgical_encounters
group by diagnosis_description
having avg(surgical_discharge_date - surgical_admission_date) <= all (
  select avg(extract(day from patient_discharge_datetime - patient_admission_datetime))
  from encounters
  group by department_id
);

--- to see units who saw all kinds of surgical cases or all types of surgical types

select unit_name, string_agg(distinct surgical_type, ',') as case_types
from surgical_encounters 
group by unit_name
having string_agg(distinct surgical_type,'-') like all(
  select string_agg(distinct surgical_type,',')
  from surgical_encounters
);

--- EXISTS used to see if subquery returns any results. used with WHERE. Can be inefficient or poor perfomance. Eg : if subquery returns null, results of EXISTS evaluates to True
--- Query : to get all encounters with at least one order
select e.*
from encounters e 
where exists (
  select 1 from orders_procedures o
  where e.patient_encounter_id = o.patient_encounter_id
) 

select p.*
from patients p
where not exists (
  select 1
  from surgical_encounters s
  where s.master_patient_id = p.master_patient_id
);