WITH a1 AS (
       select r.isbn13, min(r.rscore) as minrating, max(r.rscore) as maxrating
       from ratings r, books b
       where r.isbn13 = b.isbn13 and b.published_year >= 2000
       group by r.isbn13
       having count(*)>30 and avg(r.rscore)>= 4)
select
    b.isbn13
    , b.title
    , a1.minrating
    , count(distinct r2.rating_id) as numminrating
    , a1.maxrating
    , count(distinct r3.rating_id) as nummaxrating
    , (count(distinct r2.rating_id)::float/count(distinct r3.rating_id))::numeric(4,2) as ratio
from
   a1
   , ratings r2
   , ratings r3
   , books b
where
    a1.isbn13 = r2.isbn13
    and a1.isbn13 = r3.isbn13
    and b.isbn13 = a1.isbn13
    and r2.rscore = a1.minrating
    and r3.rscore = a1.maxrating
group by
    b.isbn13
    , a1.minrating
    , a1.maxrating
order by
    ratio desc
    , title desc
;


WITH sameauthor AS (
      select distinct a1.isbn13
      from authors a1, authors a2
      where a1.isbn13<>a2.isbn13 and a1.author = a2.author
)	    
select
    b.isbn13
    , b.title
    , count(distinct a1.author) as numauthors
from
    books b
    , authors a1
where
    b.isbn13 = a1.isbn13
    and b.isbn13 in (select isbn13 from sameauthor)
group by
    b.isbn13
having
    count(distinct a1.author)>2
order by
    isbn13 asc
;





WITH numauthors as (
   select a.isbn13, count(*) as num
   from authors a
   group by a.isbn13
)
select
    b1.isbn13 as firstisbn13
    , b1.title as firsttitle
    , b2.isbn13 as secondisbn13
    , b2.title  as secondtitle
from
    books b1
    , books b2
where
    b1.isbn13 < b2.isbn13
    and b1.title = b2.title
    and (select count(*) 
         from authors a1, authors a2
         where a1.author = a2.author 
               and a1.isbn13=b1.isbn13 
               and a2.isbn13=b2.isbn13) = 
                      (select num from numauthors a
                       where a.isbn13=b1.isbn13)
    and (select count(*) 
         from authors a1, authors a2
         where a1.author = a2.author 
               and a1.isbn13=b1.isbn13 
               and a2.isbn13=b2.isbn13) = 
                      (select num from numauthors a
                       where a.isbn13=b2.isbn13)
order by
    firstisbn13 asc;