Global Flavor Insights: A Data Dive

Comprehensive analysis project for Taste of the World Cafe, leveraging MySQL Workbench to extract actionable insights from their menu and sales data. The project aims to:

1. Explore the menu_items Table: Assess the new menu offerings to understand item variety, pricing, and changes.
2. Analyze the order_details Table: Examine the collected data on customer orders to gauge purchasing patterns and frequencies.
3. Integrate Insights from Both Tables: Evaluate customer reactions to the new menu by correlating menu item data with order trends and preferences.

The findings will reveal high-performing and underperforming dishes, shed light on customer preferences, and provide recommendations to refine menu strategy and enhance overall dining experiences. This analysis will guide Taste of the World Cafe in optimizing menu offerings and improving customer satisfaction.

---

SQL Syntax

USE restaurant_db;

SELECT * FROM menu_items;

SELECT * FROM order_details;

-- Objective 1

-- Explore the items table

SELECT * FROM menu_items;

SELECT * FROM order_details;

-- View the menu_items table and write a query to find the number of items on the menu

SELECT * FROM menu_items;

SELECT COUNT(*) AS number_of_items FROM menu_items;

-- What are the least and most expensive items on the menu?

SELECT

item_name,category,price

FROM

menu_items

WHERE

price = (SELECT MIN(price) FROM menu_items) OR 

price = (SELECT MAX(price) FROM menu_items);

-- How many Italian dishes are on the menu? What are the least and most expensive Italian dishes on the menu?

SELECT COUNT(*) AS nbr_of_italians_dishes FROM menu_items WHERE category = 'Italian';

SELECT

item_name, category, price

FROM

menu_items

WHERE

category = 'Italian'

    AND price = (SELECT 

        MIN(price)

    FROM

        menu_items

    WHERE

        category = 'Italian')

    OR price = (SELECT 

        MAX(price)

    FROM

        menu_items

    WHERE

        category = 'Italian');

-- How many dishes are in each category? What is the average dish price within each category?

SELECT

category,COUNT(DISTINCT menu_item_id) AS nbr_of_menu

FROM menu_items

GROUP BY 1;

SELECT

category,COUNT(DISTINCT menu_item_id) AS nbr_of_menu, ROUND(AVG(price),2) AS average_price

FROM menu_items

GROUP BY 1;

-- Objective 2

-- Explore the orders table

SELECT * FROM order_details;

-- View the order_details table. What is the date range of the table?

SELECT MIN(order_date) AS start_date, MAX(order_date) AS end_date FROM order_details;

-- How many orders were made within this date range? How many items were ordered within this date range?

SELECT * FROM order_details;

SELECT

COUNT(DISTINCT order_id) AS nbr_of_orders,

COUNT(DISTINCT order_details_id) AS nbr_of_items

FROM order_details;

SELECT * FROM order_details;

-- Which orders had the most number of items?

WITH item_count AS(

SELECT

order_id,

COUNT(order_details_id) AS nbr_of_items

FROM order_details

GROUP BY order_id),

max_item AS(

SELECT

MAX(nbr_of_items) AS maximum_items

FROM

item_count)

SELECT

order_id,

nbr_of_items

FROM

item_count JOIN max_item

WHERE

nbr_of_items = maximum_items;

-- How many orders had more than 12 items?

SELECT

COUNT(order_id) nbr_of_orders

FROM(

SELECT

order_id,

COUNT(order_details_id) AS nbr_of_items

FROM

order_details

GROUP BY order_id

HAVING nbr_of_items = 12) AS item_counts;

-- Objective 3

-- Analyze customer behavior

-- Combine the menu_items and order_details tables into a single table

SELECT * FROM order_details;

SELECT * FROM menu_items;

SELECT * FROM order_details JOIN menu_items ON item_id = menu_item_id;

-- What were the least and most ordered items? What categories were they in?

-- First Step : Create a join table

CREATE TEMPORARY TABLE join_table SELECT * FROM order_details JOIN menu_items ON item_id = menu_item_id;

-- Second Step : Create a min-max table

CREATE TEMPORARY TABLE min_max

SELECT

MIN(freq) AS least_freq,

MAX(freq) AS most_freq

FROM(

SELECT

item_name,

COUNT(menu_item_id) AS freq

FROM join_table

GROUP BY item_name) AS frequency;

-- Final Step : Extract item_name & category for min_freq_order & max_freq_order

SELECT

item_name,

category,

COUNT(menu_item_id) AS order_freq

FROM

join_table

GROUP BY 1,2

HAVING order_freq = 123 OR order_freq = 622;

-- What were the top 5 orders that spent the most money?

SELECT

order_id,

SUM(price) AS total_spending

FROM join_table

GROUP BY order_id

ORDER BY total_spending DESC LIMIT 5;

-- View the details of the highest spend order. Which specific items were purchased?

SELECT

item_name,

category,

price

FROM join_table

WHERE order_id = 440

ORDER BY price DESC;

-- BONUS: View the details of the top 5 highest spend orders

-- FIRST STEP: CREATE top_5_order TEMPORARY TABLE

CREATE TEMPORARY TABLE top_5_order

SELECT

order_id,

SUM(price) AS total_spending

FROM join_table

GROUP BY order_id

ORDER BY total_spending DESC LIMIT 5;

SELECT

top_5_order.order_id,

item_name,

price

FROM top_5_order INNER JOIN join_table ON top_5_order.order_id = join_table.order_id

ORDER BY top_5_order.total_spending DESC;