----- PROCEED WITH EXTREME CAUTION!!!! ------

select
  b.baker
  , count(f.episodeid) as numfavorites
  , 4
from
   bakers b
   , favorites f
where   
   b.baker = f.baker
   and f.episodeid >= 4
group by
   b.baker;


SQL - Scalar queries
----------------------


Who was the youngest baker(s)?

select min(age) from bakers;

select baker from bakers where age = 26;

select
   b.baker
from
   bakers b
   left join bakers b2
     on b.age > b2.age
where
   b2.baker is null ;


select
   baker
   , (select min(age) from bakers) as minage
from
   bakers
where
   age = (select min(age) from bakers) ;



Which episode had the highest ratings?

select
   id
from
   episodes
where
   viewers7day = (select max(viewers7day) from episodes);




SQL - Anonymous queries
----------------------

Relation = bag of tuples

find the drop in ratings for each episode

select
   e2.id
  , e2.viewers7day - e1.viewers7day as drop
from
   episodes e1
   , episodes e2
where
   e2.id = e1.id+1 ;
   


select
  d.e1
  , r.baker
  , d.drop
from
 (select
    e1.id as e1
    , e2.id as e2
    , e2.viewers7day - e1.viewers7day as drop
  from
    episodes e1
   , episodes e2
  where
    e2.id = e1.id+1) as d
 , results r
where
  d.drop < 0
  and r.episodeid = d.e1
  and r.result = 'eliminated';

Which episode had the highest drop in ratings?


select
  e1.id
  , e2.id
from
 (select
    min(e2.viewers7day - e1.viewers7day) as mindrop
  from
    episodes e1
   , episodes e2
  where
    e2.id = e1.id+1) as d
  , episodes e1
  , episodes e2
where
  e2.id = e1.id+1
  and e2.viewers7day - e1.viewers7day = d.mindrop;


select
  e1.id
  , e2.id
from
  episodes e1
  , episodes e2
where
  e2.id = e1.id+1
  and e2.viewers7day - e1.viewers7day =
           (select
              min(e2.viewers7day - e1.viewers7day) 
           from
             episodes e1
           , episodes e2
           where
            e2.id = e1.id+1);


Which baker won the star baker highest number of times?

-> a query that returns number of times a baker has won star baker
-> find max times!
-> finding the baker who had max times