Restaurant Order Analysis

1. Explore the menu_items table to get an idea of what's on the new menu

2. Explore the order_details table to get an idea of the data that's been collected

3. Use both tables to understand how customers are reacting to the new menu

SELECT 
    *
FROM
    menu_items;

SELECT 
    *
FROM
    order_details;

-- Objection 1 --

SELECT 
    COUNT(menu_item_id) AS NumOfItems
FROM
    menu_items;

-- What are the least expensive items on the menu?
SELECT 
    item_name, MIN(price) AS least_expensive
FROM
    menu_items
GROUP BY 1
ORDER BY 2
LIMIT 1;

-- What are most expensive items on the menu?
SELECT 
    item_name, MAX(price) AS most_expensive
FROM
    menu_items
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

-- What are the least and most expensive Italian dishes on the menu?
SELECT 
    item_name, price
FROM
    menu_items
WHERE
    category = 'Italian'
ORDER BY 2;

-- How many Italian dishes are on the menu?
SELECT 
    COUNT(item_name) AS NumOfDishes
FROM
    menu_items
WHERE
    category = 'Italian';

-- How many dishes are in each category?
SELECT 
    category, COUNT(menu_item_id) AS NumOfDishInCategory
FROM
    menu_items
GROUP BY 1;

-- What is the average dish price within each category?
SELECT 
    category, AVG(price) AS AvgDishPrice
FROM
    menu_items
GROUP BY 1
ORDER BY 2 DESC;

-- Objection 2 --

SELECT 
    MIN(order_date), MAX(order_date)
FROM
    order_details;

-- How many orders were made within this date range? How many items were ordered within this date range?
-- order_id refers to the orders, while order_details_id refers to the items
SELECT 
    COUNT(DISTINCT order_id) AS TotalOrders
FROM
    order_details;

SELECT 
    COUNT(order_details_id) AS TotalItems
FROM
    order_details;

-- Which orders had the most number of items?
SELECT 
    order_id, COUNT(item_id) AS Total_Items
FROM
    order_details
GROUP BY 1
ORDER BY 2 DESC;

-- How many orders had more than 12 items?
SELECT 
    COUNT(*)
FROM
    (SELECT 
        order_id, COUNT(item_id) AS Total_Items
    FROM
        order_details
    GROUP BY 1
    HAVING Total_Items >= 12
    ORDER BY 2 DESC) AS Num_items;

-- Combine the menu_items and order_details tables into a single table
SELECT 
    *
FROM
    order_details od
        LEFT JOIN
    menu_items mi ON od.item_id = mi.menu_item_id;

-- What were the least and most ordered items? What categories were they in?
SELECT 
    item_name,
    category,
    COUNT(order_details_id) AS Num_Purchases
FROM
    order_details od
        LEFT JOIN
    menu_items mi ON od.item_id = mi.menu_item_id
GROUP BY 1 , 2
ORDER BY 2;

SELECT 
    item_name,
    category,
    COUNT(order_details_id) AS Num_Purchases
FROM
    order_details od
        LEFT JOIN
    menu_items mi ON od.item_id = mi.menu_item_id
GROUP BY 1 , 2
ORDER BY 2 DESC;

-- What were the top 5 orders that spent the most money?
SELECT 
    order_id, SUM(price) Total_Amount
FROM
    order_details od
        LEFT JOIN
    menu_items mi ON od.item_id = mi.menu_item_id
GROUP BY order_id
ORDER BY 2 DESC
LIMIT 5;

-- View the details of the highest spend order. Which specific items were purchased?
SELECT 
    category, COUNT(item_id) AS Num_Items
FROM
    order_details od
        LEFT JOIN
    menu_items mi ON od.item_id = mi.menu_item_id
WHERE
    order_id = 440
GROUP BY 1;

-- BONUS: View the details of the top 5 highest spend orders
SELECT 
    order_id, category, COUNT(item_id) AS Num_Items
FROM
    order_details od
        LEFT JOIN
    menu_items mi ON od.item_id = mi.menu_item_id
WHERE
    order_id IN (440 , 2075, 1957, 330, 2675)
GROUP BY 1 , 2
ORDER BY 1 , 3 DESC;