GRAB_VIEWS_FINAL.sql

-- 1 TOP 5 RES HAVE HIGHEST RATES
SELECT RESTAURANTS.ID_RES, RESTAURANTS.STAR AS STAR FROM RESTAURANTS
ORDER BY STAR DESC
LIMIT 5;


-- 2 FIND 'CAC QUAN AN DEM'
SELECT * FROM RESTAURANTS
WHERE CLOSE_HOUR > '22:00:00' OR CLOSE_HOUR < '04:00:00'
OR OPEN_HOUR > '22:00:00' OR OPEN_HOUR < '04:00:00';


-- 3 PAYMENT METHODS USED THE MOST
SELECT * FROM (
	SELECT PAYMENTS.PAYMENT_METHOD, COUNT(PAYMENTS.ID_ORDER) AS totalPay 
	FROM PAYMENTS
	GROUP BY PAYMENTS.PAYMENT_METHOD) AS T
ORDER BY T.totalPay DESC;


-- 4 TOP BEST-SELLER FOOD IN EACH RESTAURANT
SELECT T.ID_RES, T.NAME_RES,T.FNAME, MAX(T.COUNTF) AS QUANTITY FROM (
		SELECT RESTAURANTS.ID_RES, RESTAURANTS.NAME_RES , FOODS.FNAME , SUM(ORDER_DETAILS.QUANTITY) AS COUNTF
		FROM RESTAURANTS
		JOIN FOODS ON FOODS.ID_RES = RESTAURANTS.ID_RES
		JOIN ORDER_DETAILS ON ORDER_DETAILS.ID_FOOD = FOODS.ID_FOOD
		GROUP BY FOODS.ID_FOOD) AS T
GROUP BY T.ID_RES
HAVING MAX(T.COUNTF)
ORDER BY ID_RES;


-- 5 TOP FOODS ORDERED BY CUSTOMERS UNDER 25 YEARS OLD
SELECT FOODS.FNAME AS FOODNAME, COUNT(ORDER_DETAILS.ID_ORDER) AS QUANTITY 
FROM USERS JOIN CUSTOMERS ON USERS.ID_USER = CUSTOMERS.ID_USER 
JOIN ORDERS ON CUSTOMERS.ID_CUS = ORDERS.ID_CUS 
JOIN ORDER_DETAILS ON ORDER_DETAILS.ID_ORDER = ORDERS.ID_ORDER 
JOIN FOODS ON ORDER_DETAILS.ID_FOOD = FOODS.ID_FOOD
WHERE (YEAR(NOW()) - YEAR(USERS.BIRTHDATE)) > 0
AND (YEAR(NOW()) - YEAR(USERS.BIRTHDATE)) < 25
GROUP BY FOODS.FNAME
ORDER BY QUANTITY DESC
LIMIT 5;

-- 6 TOP CUSTOMERS HAVE HIGHEST SPENDING IN SPECIFIC MONTH
SELECT USERS.ID_USER, CONCAT(FIRST_NAME, ' ', LAST_NAME) AS 'CUSTOMER NAME', SUM(PAYMENTS.TOTAL_PRICE) AS 'SPENDING' 
FROM USERS JOIN CUSTOMERS ON USERS.ID_USER = CUSTOMERS.ID_USER 
	JOIN ORDERS ON ORDERS.ID_CUS = CUSTOMERS.ID_CUS 
	JOIN PAYMENTS ON PAYMENTS.ID_ORDER = ORDERS.ID_ORDER
WHERE MONTH(ORDER_TIME) = 5 AND YEAR(ORDER_TIME) = 2021
GROUP BY USERS.ID_USER
ORDER BY SPENDING DESC
LIMIT 5;


-- 7 TOTAL REVENUE OF EACH MONTH
SELECT MONTH(ORDER_TIME) AS MONTH, SUM(TOTAL_PRICE) AS TOTAL_SPENDING
FROM ORDERS JOIN PAYMENTS ON ORDERS.ID_ORDER = PAYMENTS.ID_ORDER
GROUP BY MONTH(ORDER_TIME)
ORDER BY MONTH;

-- 8 TOP DRIVERS HAVE HIGHEST REVENUE IN SPECIFIC MONTH
SELECT ID_DRIVER, SUM(PAYMENTS.SHIP_COST) AS TOTAL_REVENUE
FROM ORDERS JOIN PAYMENTS ON ORDERS.ID_ORDER = PAYMENTS.ID_ORDER
WHERE MONTH(ORDER_TIME) = 5
GROUP BY ID_DRIVER
ORDER BY TOTAL_REVENUE DESC;
    
-- 9 DRIVERS MANNER
SELECT ORDERS.ID_DRIVER, CONCAT(FIRST_NAME, ' ', LAST_NAME) AS NAME, STAR, COUNT(ORDERS.ID_ORDER) AS SOCHUYEN
FROM DRIVERS JOIN USERS ON DRIVERS.ID_USER = USERS.ID_USER 
    JOIN ORDERS ON ORDERS.ID_DRIVER = DRIVERS.ID_DRIVER
GROUP BY ORDERS.ID_DRIVER
ORDER BY STAR DESC;


-- 10 CUSTOMERS WHO HAVE BIRTHDAY THIS MONTH AND HAVE SPENT > 700000
SELECT USERS.ID_USER, USERS.LAST_NAME AS 'CUSTOMER NAME', USERS.BIRTHDATE AS BIRTHDATE, SUM(PAYMENTS.TOTAL_PRICE) AS TOTAL_SPENDING
FROM USERS JOIN CUSTOMERS ON USERS.ID_USER = CUSTOMERS.ID_USER 
	JOIN ORDERS ON ORDERS.ID_CUS = CUSTOMERS.ID_CUS 
	JOIN PAYMENTS ON PAYMENTS.ID_ORDER = ORDERS.ID_ORDER
WHERE MONTH(USERS.BIRTHDATE) = MONTH(NOW())
GROUP BY USERS.ID_USER
HAVING TOTAL_SPENDING > 700000;





-- 1 VIEW CUSTOMER INFORMATION
CREATE VIEW CUSTOMER_INFORMATION AS
SELECT C.ID_CUS, U.LAST_NAME, U.FIRST_NAME, U.GENDER, U.PHONE, LOCATION FROM USERS U
JOIN CUSTOMERS C ON C.ID_USER = U.ID_USER JOIN LOCATIONS L ON L.ID_LOCATION = C.ID_LOCATION;

SELECT * FROM CUSTOMER_INFORMATION;


-- 2 VIEW BILL
CREATE VIEW BILL AS
SELECT O.ID_ORDER,
		R.NAME_RES AS RESTAURANT,
		GROUP_CONCAT(F.FNAME SEPARATOR ', ' ) AS MENU,
		CONCAT(Z.DRINAME1, ' ', Z.DRINAME2) AS DRIVER,
        CONCAT(Z.CUSNAME1, ' ', Z.CUSNAME2) AS CUSTOMER,
        L.LOCATION AS LOCATION,
        P.IS_PAID,
        P.TOTAL_PRICE AS TOTAL
FROM ORDERS O
JOIN ORDER_DETAILS OD ON OD.ID_ORDER = O.ID_ORDER
JOIN FOODS F ON F.ID_FOOD = OD.ID_FOOD
JOIN RESTAURANTS R ON R.ID_RES = O.ID_RES
JOIN LOCATIONS L ON L.ID_LOCATION = O.ID_LOC_CUS
JOIN PAYMENTS P ON P.ID_ORDER = O.ID_ORDER
JOIN (SELECT * 
	FROM (SELECT ID_ORDER AS OD1, O.ID_CUS AS CUSID, FIRST_NAME AS CUSNAME1, LAST_NAME AS CUSNAME2
		FROM ORDERS O 
		JOIN CUSTOMERS C ON O.ID_CUS = C.ID_CUS 
		JOIN USERS U ON U.ID_USER = C.ID_USER ) AS A
		JOIN (SELECT ID_ORDER AS OD2, O.ID_DRIVER AS DRIID, FIRST_NAME AS DRINAME1 , 
		LAST_NAME AS DRINAME2 
		FROM ORDERS O 
		JOIN DRIVERS C ON O.ID_DRIVER = C.ID_DRIVER 
		JOIN USERS U ON U.ID_USER = C.ID_USER ) AS B
ON A.OD1 = B.OD2) AS Z ON O.ID_ORDER = Z.OD2
GROUP BY O.ID_ORDER;

SELECT * FROM BILL;


-- 3 VIEW RESTAURANTS HAVE HIGHEST REVENUE EACH DISTRICT
CREATE VIEW DHR AS
SELECT T.ID_LOCATION, T.LOCATION, T.NAME_RES AS RESTAURANT, MAX(T.REVENUE) AS 'TOTAL REVENUE' FROM
(SELECT LOCATIONS.ID_LOCATION, LOCATIONS.LOCATION, NAME_RES, SUM(PAYMENTS.RES_COST) AS REVENUE
FROM RESTAURANTS JOIN ORDERS ON RESTAURANTS.ID_RES = ORDERS.ID_RES 
	JOIN PAYMENTS ON PAYMENTS.ID_ORDER = ORDERS.ID_ORDER 
	JOIN LOCATIONS ON RESTAURANTS.ID_LOCATION = LOCATIONS.ID_LOCATION
GROUP BY RESTAURANTS.ID_RES) AS T 
GROUP BY T.ID_LOCATION
ORDER BY ID_LOCATION;

SELECT * FROM DHR;

    
-- 4 VIEW DISTRICTS HAVE THE MOST ORDERS
CREATE VIEW MOST_ORDERS AS
SELECT LOCATIONS.LOCATION, COUNT(ORDERS.ID_ORDER) AS TOTAL_ORDER
FROM LOCATIONS JOIN RESTAURANTS ON LOCATIONS.ID_LOCATION = RESTAURANTS.ID_LOCATION 
	JOIN ORDERS ON ORDERS.ID_RES = RESTAURANTS.ID_RES
GROUP BY LOCATIONS.ID_LOCATION
ORDER BY TOTAL_ORDER DESC 
LIMIT 3;

SELECT * FROM MOST_ORDERS;

-- 5 VIEW RESTAURANT WHICH HAVE HIGHEST STAR IN EACH DISTRICT
CREATE VIEW HIGH_RES_DISTRICT AS
SELECT NAME_RES, MAX(STAR) AS STAR, LOCATION 
FROM RESTAURANTS JOIN LOCATIONS ON RESTAURANTS.ID_LOCATION = LOCATIONS.ID_LOCATION
GROUP BY LOCATIONS.ID_LOCATION;

SELECT * FROM HIGH_RES_DISTRICT;