Zomatodb-Analysis-script.sql

-- 1. List the top 3 customers who have placed the highest number of orders.

SELECT customer_name,COUNT(Order_id)
FROM customer AS C 
INNER JOIN order_detail AS OD
ON C.customer_id = OD.customer_id
GROUP BY C.customer_id 
ORDER BY COUNT(Order_id) DESC
LIMIT 3;

-- 2. Find the restaurant that has received the highest average rating.

SELECT restaurant_id,restaurant_name,rrating
FROM restaurant
ORDER BY rrating DESC 
LIMIT 1;

-- 3. List all orders from order_detail that were delivered in under 30 minutes.

SELECT *
FROM order_detail
WHERE TIMEDIFF( delivered_time,order_time )<'00:30:00' ;

-- 4. Calculate the total revenue generated by each food item. Display food_id, food_name and Total revenue 
-- ordered by total revenue in descending order.

SELECT F.food_id,F.food_name,SUM(quantity*price_per_unit) AS Total_revenue
FROM order_food AS ODF
INNER JOIN foods AS F
ON ODF.food_id = F.food_id
GROUP BY F.food_id
ORDER BY Total_revenue DESC;

-- 5. Find the second highest revenue-generating restaurant.

WITH XYZ AS
(SELECT ROW_NUMBER() OVER(ORDER BY SUM(quantity*price_per_unit) DESC) AS Rank_, R.restaurant_id,R.restaurant_name,
SUM(quantity*price_per_unit) AS Total_revenue
FROM restaurant AS R
INNER JOIN  order_food AS ODF
ON ODF.restaurant_id = R.restaurant_id
INNER JOIN Foods AS F
ON F.food_id = ODF.food_id
GROUP BY  R.restaurant_id)

SELECT *
FROM XYZ
WHERE Rank_= 2;

-- 6. Find the 5 most popular food items based on the quantity sold.
 
SELECT F.food_id,F.food_name,SUM(Quantity) AS Total_QuantitySold
FROM order_food AS ODF
INNER JOIN foods AS F
ON ODF.food_id =F.food_id
GROUP BY F.food_id
ORDER BY Total_QuantitySold DESC
LIMIT 5 ;

-- 7. List the top 3 Zomato employees with the highest average delivery ratings.

SELECT employee_id,employee_name,employee_avg_rating
FROM zomato_employee
ORDER BY employee_avg_rating DESC
LIMIT 3;

-- 8. Determine the month with the highest number of total orders placed.

SELECT MONTHNAME(order_time) AS Month_Name,COUNT(order_id) AS Total_Orders
FROM order_detail
GROUP BY Month_Name
LIMIT 1;

-- 9. Calculate the average order amount for each customer. Order by average order amount in descending order.

SELECT C.customer_id,C.customer_name,ROUND(AVG(quantity*price_per_unit),2) AS average_order_amount
FROM foods AS F
INNER JOIN order_food AS ODF
ON F.food_id = ODF. food_id
INNER JOIN customer AS C
ON C.customer_id = ODF.customer_id
GROUP BY customer_id
ORDER BY average_order_amount DESC;

-- 10. Identify the most frequent customer for each restaurant.

-- ANS 1

WITH Abc AS 
(SELECT R.restaurant_id,
R.restaurant_name,
C.customer_id,
C.customer_name,
COUNT(OD.order_id) AS ORDER_COUNT,
ROW_NUMBER() OVER(PARTITION BY R.restaurant_id ORDER BY COUNT(OD.order_id) DESC ) AS Rank_
FROM restaurant AS R
INNER JOIN order_detail AS OD
ON R.restaurant_id = OD.restaurant_id
INNER JOIN customer AS C 
ON OD.customer_id = C.customer_id
GROUP BY R.restaurant_id,C.customer_id
HAVING ORDER_COUNT = (SELECT COUNT(order_id) AS Highest_Order_Count
                      FROM order_detail AS O
                      WHERE R.restaurant_id =O.restaurant_id
                      GROUP BY O.customer_id
                      ORDER BY Highest_Order_Count DESC
                      LIMIT 1))
                      
SELECT * 
FROM Abc
WHERE Rank_=1 ;           

-- ANS 2

SELECT R.restaurant_id,
R.restaurant_name,
C.customer_id,
C.customer_name,
COUNT(OD.order_id) AS ORDER_COUNT,
ROW_NUMBER() OVER(PARTITION BY R.restaurant_id ORDER BY COUNT(OD.order_id) DESC ) AS Rank_
FROM restaurant AS R
INNER JOIN order_detail AS OD
ON R.restaurant_id = OD.restaurant_id
INNER JOIN customer AS C 
ON OD.customer_id = C.customer_id
GROUP BY R.restaurant_id,C.customer_id
HAVING ORDER_COUNT = (SELECT COUNT(order_id) AS Highest_Order_Count
                      FROM order_detail AS O
                      WHERE R.restaurant_id =O.restaurant_id
                      GROUP BY O.customer_id
                      ORDER BY Highest_Order_Count DESC
                      LIMIT 1);    
                      
                      
-- 11. Calculate the total number of orders placed on weekends. 

SELECT COUNT(order_id)
FROM order_detail
WHERE DATE_FORMAT(order_time,'%W') IN ('Saturday','SUNDAY');

-- 12. Calculate the average delivery time (in minutes) for orders placed on weekdays versus weekends.

SELECT
(SELECT TIME_FORMAT(sec_to_time(AVG(TIME_TO_SEC(TIMEdiff(delivered_time,order_time)))),'%i:%s') 
FROM order_detail
WHERE NOT DATE_FORMAT(Order_time,'%W') IN ('Saturday','Sunday')) AS Weekday_AvgDeliveryTime,
(SELECT TIME_FORMAT(sec_to_time(AVG(TIME_TO_SEC(TIMEdiff(delivered_time,order_time)))),'%i:%s') 
FROM order_detail
WHERE DATE_FORMAT(Order_time,'%W') IN ('Saturday','Sunday')) AS Weekend_AvgDeliveryTime;

-- 13. List the top 5 most expensive food items. Display food_id, food_name, and price_per_unit for all food items with the highest 5 prices.

WITH ABC AS
(SELECT food_id, food_name,price_per_unit,DENSE_RANK() OVER(ORDER BY price_per_unit DESC) AS RANK_
FROM foods)

SELECT * 
FROM ABC 
WHERE Rank_<=5;

-- 14. Find the restaurant with the most diverse menu (i.e., the highest number of food items).

WITH XYZ AS 
(SELECT R.restaurant_name,f.food_name
FROM restaurant AS R
INNER JOIN order_food AS O
ON R.restaurant_id=O.restaurant_id
INNER JOIN foods AS F
ON F.food_id = O.food_id
GROUP BY r.restaurant_id,F.food_id)

SELECT restaurant_name,COUNT(food_name) AS DISH_COUNT
FROM XYZ
GROUP BY restaurant_name
ORDER BY DISH_COUNT DESC
LIMIT 1;

-- 15. Calculate total payment amount for each payment type.

SELECT payment_type,SUM(quantity*price_per_unit) AS Total_payment_amt
FROM payment_table AS P
INNER JOIN order_food AS O
ON P.order_id = O.order_id
INNER JOIN foods AS F
ON F.food_id = O.food_id
GROUP BY P.payment_type;