Published on

Restaurant Order Analysis

Authors
  • avatar
    Name
    Dhiraj Das
    Twitter

Introduction

This is my solution to a project by Maven Analytics. A dataset consisting of a quarter's worth of orders from a fictitious international cuisine restaurant. The challenge was to analyse the menu items and the order details and understand the customer behavior.

The Tools and Dataset

  • MySQL Workbench Community Edition - One of the most commonly used tool to connect to a MySQL server. Its free and works on my mac hence I use it.
  • Aiven - A cloud platform that lets users host one instance of a MySQL, PostgreSQL, and Redis each for free. I am running out space on my laptop so I used Aiven to create the database on their servers and connect to it over the internet.
  • Dataset - The Maven Analytics platform came with a data dictionary and a SQL script to create the database. The same was used to create the db schema and neccessary tables.

Objective 1 - Explore the items table

The first objective was to better understand the items table by finding the number of rows in the table, the least and most expensive items, and the item prices within each category. Here are the tasks -

  • View the menu_items table and write a query to find the number of items on the menu
select count(*) 
from menu_items;
  • What are the least and most expensive items on the menu?
select 
	min(price) as least_expensive,
    max(price) as most_expensive
from 
menu_items;
  • How many Italian dishes are on the menu? What are the least and most expensive Italian dishes on the menu?
select
	category,
    count(*),
	min(price) as least_expensive,
    max(price) as most_expensive
from menu_items
group by category;
  • How many dishes are in each category? What is the average dish price within each category?
select
	category,
    count(*),
	avg(price) as avg_price
from menu_items
group by category;

Objective 2 - Explore the orders table

The second objective was to better understand the orders table by finding the date range, the number of items within each order, and the orders with the highest number of items.

  • View the order_details table. What is the date range of the table?
select * from order_details;

select 
	min(order_date) as min_date,
    max(order_date) as max_date
from order_details;
  • How many orders were made within this date range? How many items were ordered within this date range?
select 
    count(distinct order_id) as order_count, 
    count(item_id) as item_count
from order_details;
  • Which orders had the most number of items?
select
	order_id,
    count(item_id) as item_count
from order_details
group by 1
order by count(item_id) desc;
  • How many orders had more than 12 items?
select
	order_id,
    count(item_id) as item_count
from order_details
group by 1
having count(item_id) > 12

Objective 3 - Analyze customer behavior

The final objective was to combine the items and orders tables, find the least and most ordered categories, and dive into the details of the highest spend orders.

  • Combine the menu_items and order_details tables into a single table
select *
from order_details a
left join menu_items b
on a.item_id = b.menu_item_id;
  • What were the least and most ordered items? What categories were they in?
select 
	item_name,
    category,
    count(menu_item_id) as item_count
from order_details a
inner join menu_items b
on a.item_id = b.menu_item_id
group by item_name, category
order by item_count;
  • What were the top 5 orders that spent the most money?
select
	order_id,
    sum(price)
from order_details a
left join menu_items b
on a.item_id = b.menu_item_id
group by 1
order by 2 desc
limit 5;
  • View the details of the highest spend order. Which specific items were purchased?
  • BONUS: View the details of the top 5 highest spend orders
with top_5_orders as (
	select
		order_id,
		sum(price)
	from order_details a
	left join menu_items b
	on a.item_id = b.menu_item_id
	group by 1
	order by 2 desc
	limit 5
)

select 
    * 
from order_details a
inner join menu_items b
on a.item_id = b.menu_item_id
where order_id in (select order_id from top_5_orders);

GitHub project repo