Restaurant Order Analysis

Project Goal

Exploring customer data from a fictitious international cuisine restaurant to see which menu items are doing well / not well and what the top customers seem to like best.

Project Objectives

1. Explore the items table
2. Explore the orders table
3. Analyze customer behavior

Data Set

Restaurant Orders - A quarter's worth of orders from a fictitious international cuisine restaurant

Project Execution and Results

1. Explore the items table

• Number of items on the menu: 32
  • SELECT COUNT(item_name) FROM menu_items;
• Most expensive item: Shrimp Scampi ($19.95)
  • SELECT item_name, price FROM menu_items
  • ORDER BY price DESC
  • LIMIT 1;
• Least expensive item: Edameme ($5.00)
  • SELECT item_name, price FROM menu_items
  • ORDER BY price ASC
  • LIMIT 1;
• Italian dishes on the menu: 9
  • SELECT COUNT(item_name) FROM menu_items
  • WHERE category = "Italian";
• Most expensive Italian item: Shrimp Scampi ($19.95)
  • SELECT item_name, price FROM menu_items
  • WHERE category = "Italian"
  • ORDER BY price DESC
  • LIMIT 1;
• Least expensive Italian item: Spaghetti ($14.50)
  • SELECT item_name, price FROM menu_items
  • WHERE category = "Italian"
  • ORDER BY price ASC
  • LIMIT 1;
• Dishes in each category: - American - 6 items - $10.07 average price - Asian - 8 items - $13.48 average price - Mexican - 9 items - $11.80 average price - Italian - 9 items - $16.75 average price
  • SELECT category, COUNT(item_name) AS items, ROUND(AVG(price), 2) AS avg_price FROM menu_items
  • GROUP BY category;

2. Explore the orders table

• Date range: 2023-01-01 - 2023-03-31
  • SELECT MIN(order_date) AS min_date, MAX(order_date) AS max_date FROM order_details;
• Orders made within date range: 5370
  • SELECT COUNT(DISTINCT order_id) AS orders FROM order_details
  • WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';
• Items ordered within date range: 12234
  • SELECT COUNT(DISTINCT order_details_id) AS ordered_items FROM order_details
  • WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';
• Orders with most number of items: order_id 330, 440, 443, 1957, 2675, 3473, 4305, 4482 (14 items)
  • SELECT order_id, COUNT(DISTINCT order_details_id) AS order_items FROM order_details
  • WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31'
  • GROUP BY order_id
  • ORDER BY COUNT(DISTINCT order_details_id) DESC;
• Orders with more than 12 items: 23
  • SELECT COUNT(order_id) FROM
  • (
  • SELECT order_id, COUNT(DISTINCT order_details_id) AS order_items FROM order_details
  • WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31'
  • GROUP BY order_id
  • ORDER BY COUNT(DISTINCT order_details_id) DESC
  • ) AS orders_with_item_count
  • WHERE order_items > 12;

3. Analyze customer behavior

• Combine the menu_items and order_details tables into a single table
  • CREATE TEMPORARY TABLE combined_order_table
  • SELECT * FROM order_details
  • LEFT JOIN menu_items
  • ON order_details.item_id = menu_items.menu_item_id;
• Most orders item: Hamburger (American, 622 orders)
  • SELECT category, item_name, COUNT(menu_item_id) AS orders FROM combined_order_table
  • GROUP BY category, item_name
  • ORDER BY count(menu_item_id) DESC
  • LIMIT 1;
• Least ordered item: Chicken Tacos (Mexican, 123 orders)
  • SELECT category, item_name, COUNT(menu_item_id) AS orders FROM combined_order_table
  • GROUP BY category, item_name
  • ORDER BY count(menu_item_id) ASC
  • LIMIT 1;
• Top 5 orders by spend: 440 ($192.15), 2075 ($191.05), 1957 ($190.10), 330 ($189.70), 2675 ($185.10)
  • SELECT order_id, SUM(price) FROM combined_order_table
  • GROUP BY order_id
  • ORDER BY SUM(price) DESC
  • LIMIT 5;
• Details of the highest spend order: -
  • SELECT category, item_name, price FROM combined_order_table
  • WHERE order_id = '440';
• Details of the 5 highest spend orders: -
  • SELECT order_id, category, item_name, price FROM combined_order_table
  • WHERE order_id IN(440, 2075, 1957, 330, 2675);