How I Used SQL to Analyze Data and Make Better Business Decisions
- Daniel Cortes
- May 12, 2023
- 4 min read
As a data analyst, I'm always on the lookout for ways to streamline and optimize my work. Recently, I had the opportunity to work on a project that required me to analyze a large dataset to identify patterns and trends that could help improve business performance. In this post, I'll share my experience of using SQL to analyze the data and how it helped me make better business decisions.
Data Sample From Mavic
To get started, I imported the dataset into a SQL database and began querying the data using SQL commands.
USE PizzaPlace
-- Average Customers per day---
SELECT ceiling(cast(count(order_id) as decimal)/ cast(count(distinct date) as decimal)) as Average_Customers_Per_Day
--60--
-- Customers per hour--
SELECT datepart(hour, time) as Hour, count(order_id) as Customers_per_Hour
FROM Orders
GROUP BY datepart(hour, time)
ORDER BY Hour Hour Customers_per_Hour
9 1
10 8
11 1231
12 2520
13 2455
14 1472
15 1468
16 1920
17 2336
18 2399
19 2009
20 1642
21 1198
22 663
23 28
The number of customers per hour varies greatly throughout the day. The busiest hours appear to be from 11 am to 6 pm, with a peak at 12 pm with 2520 customers. The hours with the lowest number of customers are before 10 am and after 8 pm.
-- Averrage Pizzas per order --
SELECT ROUND((SUM(quantity) / COUNT(DISTINCT order_id)), 2) AS Average_Pizzas_Per_Order
FROM OrderDetailsFROM OrderDetails
-- 2,32 --
--Pizzas sold by size--
SELECT p.size, COUNT(od.quantity) AS Total_Pizzas_Sold,
CASE
WHEN p.size = 'S' THEN 1
WHEN p.size = 'M' THEN 2
WHEN p.size = 'L' THEN 3
WHEN p.size = 'XL' THEN 4
WHEN p.size = 'XXL' THEN 5
ELSE 6
END AS Sort_Column
FROM Pizzas p
JOIN OrderDetails od ON od.pizza_id = p.pizza_id
GROUP BY p.size
ORDER BY Sort_Column; size Total_Pizzas_Sold Sort_Column
S 14137 1
M 15385 2
L 18526 3
XL 544 4
XXL 28 5
The most popular pizza size sold is Large (L), with a total of 18,526 pizzas sold. Medium (M) comes in second place with 15,385 pizzas sold, and Small (S) is in third place with 14,137 pizzas sold.
--Best Sellers --
SELECT TOP 5 pt.name, p.size, sum(od.quantity) as total_pizzas_Sold
FROM Pizzas p
JOIN PizzaTypes pt on pt.pizza_type_id = p.pizza_type_id
JOIN OrderDetails od on od.pizza_id = p.pizza_id
GROUP BY pt.name, p.size
ORDER BY total_pizzas_Sold descname size total_pizzas_Sold
The Big Meat Pizza S 1914
The Thai Chicken Pizza L 1410
The Five Cheese Pizza L 1409
The Four Cheese Pizza L 1316
The Classic Deluxe Pizza M 1181
From this results the pizza place can optimize its inventory management. For example, the owner can ensure that it has enough ingredients and supplies to make the most popular pizzas in the sizes that are most frequently ordered. This can help reduce waste and increase profitability by ensuring that the business is using its resources efficiently.
--Worst Sellers --
SELECT TOP 5 pt.name, p.size, sum(od.quantity) as total_pizzas
FROM Pizzas p
JOIN PizzaTypes pt on pt.pizza_type_id = p.pizza_type_id
JOIN OrderDetails od on od.pizza_id = p.pizza_id
GROUP BY pt.name, p.size
ORDER BY total_pizzas
name size total_pizzas
The Greek Pizza XXL 28
The Green Garden Pizza L 95
The Chicken Alfredo Pizza S 96
The Calabrese Pizza S 99
The Mexicana Pizza S 162
From the data we can conclude the business may need to adjust the pricing, the recipe, or the marketing strategy for these pizzas. Alternatively, the owner may decide to discontinue these pizzas altogether if they are not profitable or not in demand by customers.
--How much money did we make this year?--
WITH PizzaRevenue AS (
SELECT pt.name, round(SUM(od.quantity),0) * round(AVG(p.price),0) AS revenue
FROM Pizzas p
JOIN PizzaTypes pt ON pt.pizza_type_id = p.pizza_type_id
JOIN OrderDetails od ON od.pizza_id = p.pizza_id
GROUP BY pt.name
)
SELECT SUM(revenue) AS total_revenue
FROM PizzaRevenue;
-- $815.646 --
-- Total Revenue TOP 10 PizzaS --
SELECT TOP 10 pt.name, round(avg(p.price),2) as Average_Pizza_Price, sum(od.quantity) as Quantity_Sold,
(round(avg(p.price),2) *sum(od.quantity)) as Revenue_per_Pizza
FROM Pizzas p
JOIN PizzaTypes pt on pt.pizza_type_id = p.pizza_type_id
JOIN OrderDetails od on od.pizza_id = p.pizza_id
GROUP BY pt.name, od.quantity
ORDER BY Revenue_per_Pizza descname Revenue_per_Pizza
The Thai Chicken Pizza 41245
The Barbecue Chicken Pizza 40668,96
The California Chicken Pizza 38973,48
The Classic Deluxe Pizza 37135,38
The Spicy Italian Pizza 33521,2
The Southwest Chicken Pizza 33465,18
The Italian Supreme Pizza 32234,78
The Hawaiian Pizza 30865,89
The Four Cheese Pizza 30458,12
The Sicilian Pizza 29334,48
-- Sales by Month --
SELECT left(DATENAME(month, date),3) AS Months,
COUNT(order_id) AS Orders
FROM Orders
GROUP BY DATENAME(month, date)
ORDER BY CASE
WHEN left(DATENAME(month, date),3) = 'Jan' then 1
WHEN left(DATENAME(month, date),3) = 'FEB' then 2
WHEN left(DATENAME(month, date),3) = 'MAR' then 3
WHEN left(DATENAME(month, date),3) = 'APR' then 4
WHEN left(DATENAME(month, date),3) = 'MAY' then 5
WHEN left(DATENAME(month, date),3) = 'JUN' then 6
WHEN left(DATENAME(month, date),3) = 'JUL' then 7
WHEN left(DATENAME(month, date),3) = 'AUG' then 8
WHEN left(DATENAME(month, date),3) = 'SEP' then 9
WHEN left(DATENAME(month, date),3) = 'OCT' then 10
WHEN left(DATENAME(month, date),3) = 'NOV' then 11
else 12
ENDMonths Orders
Jan 1845
Feb 1685
Mar 1840
Apr 1799
May 1853
Jun 1773
Jul 1935
Aug 1841
Sep 1661
Oct 1646
Nov 1792
Dec 1680
From the observed results the business can identify patterns in customer behavior and to plan for staffing and inventory needs. For example, the business can use this data to determine which months may require more staff to handle the higher volume of orders, and which months may require less staff due to lower order volume. The business can also use this data to predict future sales and adjust its inventory levels accordingly.
--Total Pizzas Sold--
select sum(quantity) as Total_Pizzas_Sold
from OrderDetails
--49574--
Comments