Comparing price range and num of pizza between restaurants in SQL

Question

Tables in SQL Query

1. Likes(cname, pizza)

2. Customers(cname, area)

3. Restaurants(rname, area)

4. Sells (rname, pizza, price)

Refer to: http://sqlfiddle.com/#!9/06ade3/6 (Consist of code and database schema)

Expected Results

| rname | 
---------
|   D   |  
|   L   |  

--> Output a list all restaurants R such that there does not exist any restaurant R2 that is more diverse than R.)

A restaurant is more diverse if:

1. priceRange of R1 >= priceRange of R2 AND numPizza of R1 > numPizza of R2

   OR

   priceRange of R1 > priceRange of R2 AND numPizza of R1 >= numPizza of R2

2. If the restaurant does not sell any pizza, numPizza = 0 and priceRange = 0

** priceRange refers to max-min of the restaurant.

** numPizza refers to total number of pizza in the restaurant

My SQL Code:

SELECT r1.rname
FROM restaurants r1
INNER JOIN restaurants r2 ON r1.rname < r2.rname
WHERE (SELECT (MAX(s1.price)-MIN(s1.price) AS s1.pricerange)
   FROM sells s1
   INNER JOIN sells s2 ON s1.rname < s2.rname)
   WHERE s1.pricerange > MAX(s1.price)-MIN(s1.price) AS s2.pricerange
   AND COUNT(s1.pizza) >= COUNT(s2.pizza)
   )

OR (SELECT (MAX(s1.price)-MIN(s1.price) AS s1.pricerange)
   FROM sells s1
   INNER JOIN sells s2 ON s1.rname < s2.rname)
   WHERE s1.pricerange >= MAX(s1.price)-MIN(s1.price) AS s2.pricerange
   AND COUNT(s1.pizza) > COUNT(s2.pizza)
   )

The way that i implement it seems to be wrong. The last part of my code looks really similar to the second last part. Except for the inequality signs. Is there a better way to do this?

Answer 1

Create a temporary table such as below then run the query. The logic can be simplified as "collect all restaurants having maximum pricerange or maximum numpizza. Hope this helps. Thanks.

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS 
(select r.rname,
max(s.price)-min(s.price) as pricerange,
count(1) as numpizzas
from restaurants r
inner join sells s on r.rname=s.rname
inner join pizzas p on s.pizza=p.pizza
group by r.rname)

SQL: 
select t1.rname 
from table2 t1
inner join (
select max(pricerange) as maxpricerange
from table2) t2 on t1.pricerange=t2.maxpricerange
union
select t1.rname 
from table2 t1
inner join (
select max(numpizzas) as maxnumpizzas
from table2) t2 on t1.numpizzas=t2.maxnumpizzas

Result: 
     rname
     D
     L