POS_Performance_Analysis.sql

-- PROJECT OBJECTIVES:
-- 1. Dig into the customer datastet to see which menu items are doing well/not well
-- 2. See what the top customers seem to like best
-- 3. See how customers are reacting to the menu

SELECT * FROM public.menu_items
SELECT * FROM public.order_details

-- How many items are on the menu?
SELECT COUNT (menu_item_id) FROM public.menu_items

-- What are the most expensive items on the menu?
SELECT item_name, price FROM public.menu_items
ORDER BY price DESC
LIMIT 10

-- What are the least expensive items on the menu?
SELECT item_name, price FROM public.menu_items
ORDER BY price
LIMIT 10

-- How many italian dishes are on the menu?
SELECT COUNT (category) FROM public.menu_items AS Italian_dishes
WHERE category ILIKE 'italian'

-- What are the least and most expensive italian dishes on the menu?
SELECT MIN(price) FROM public.menu_items
WHERE category ILIKE 'italian'

SELECT MAX(price) FROM public.menu_items
WHERE category ILIKE 'italian'

SELECT MIN(price), MAX(price) FROM public.menu_items
WHERE category ILIKE 'italian'

-- How many dishes are in each category?
SELECT COUNT (item_name), category FROM public.menu_items
GROUP BY category

-- What is the average dish price within each category?
SELECT category, AVG(price) FROM public.menu_items
GROUP BY category


-- What is the date range of the table?
SELECT * FROM public.order_details

SELECT MIN(order_date), MAX (order_date) FROM public.order_details

-- How many orders were made within this date range?
SELECT COUNT(DISTINCT(order_id))FROM public.order_details


-- How many items were ordered within this date range?
SELECT COUNT (*) FROM public.order_details



SELECT * FROM public.menu_items
SELECT * FROM public.order_details

-- Which orders had the most number of items?

SELECT order_id, COUNT(item_id) FROM public.order_details
GROUP BY order_id
ORDER BY COUNT(item_id) DESC

-- How many orders had more than 12 items?
SELECT COUNT(*) FROM(SELECT order_id, COUNT(item_id) FROM public.order_details
GROUP BY order_id
HAVING COUNT(item_id) > 12) AS item_count


--What were the least and most ordered items?
SELECT * FROM public.menu_items
SELECT * FROM public.order_details

SELECT * FROM public.menu_items pmi
LEFT JOIN public.order_details pod
ON pmi.menu_item_id = pod.item_id
--Most
SELECT item_name, COUNT(order_details_id) 
FROM public.menu_items pmi
LEFT JOIN public.order_details pod
ON pmi.menu_item_id = pod.item_id
GROUP BY item_name
ORDER BY COUNT(order_details_id) DESC

--Least
SELECT item_name, COUNT(order_details_id) 
FROM public.menu_items pmi
LEFT JOIN public.order_details pod
ON pmi.menu_item_id = pod.item_id
GROUP BY item_name
ORDER BY COUNT(order_details_id) ASC

-- What categories were they in?
SELECT item_name, category, COUNT(order_details_id) 
FROM public.menu_items pmi
LEFT JOIN public.order_details pod
ON pmi.menu_item_id = pod.item_id
GROUP BY item_name, category
ORDER BY COUNT(order_details_id) DESC

-- What were the top 5 orders that spent the most money?
SELECT order_id, SUM(price) AS total_spend 
FROM public.menu_items pmi
LEFT JOIN public.order_details pod
ON pmi.menu_item_id = pod.item_id
GROUP BY order_id
ORDER BY total_spend DESC
LIMIT 5

--View the details of the highest spend order
SELECT category, COUNT(item_id) as num_items
FROM public.menu_items pmi
LEFT JOIN public.order_details pod
ON pmi.menu_item_id = pod.item_id
WHERE order_id = 440
GROUP BY category

--View the details of the top 5 highest spend orders
SELECT category, COUNT(item_id) as num_items
FROM public.menu_items pmi
LEFT JOIN public.order_details pod
ON pmi.menu_item_id = pod.item_id
WHERE order_id IN (440, 2075, 1957, 330, 2675)
GROUP BY category


SELECT order_id, category, COUNT(item_id) as num_items
FROM public.menu_items pmi
LEFT JOIN public.order_details pod
ON pmi.menu_item_id = pod.item_id
WHERE order_id IN (440, 2075, 1957, 330, 2675)
GROUP BY order_id, category