Coffee Shop Sales Analysis Project with SQL
Coffee is one of the most popular beverages in the world. This project aims to analyze café sales data in New York City, from 2023 to understand patterns and provide valuable information that will be used to support business decision making.
Data Source
Data was collected from Maven Analytics Website. The data contains 65536 records from three branches in the city.
Analysis Tools
SQL Server Database was used to store and query the dataset
Questions
In data analysis, there must be questions that must be answered by performing queries on the data.
The sales of each branch
Sales by category and type
Find out the hours during which most sales take place
The best-selling items by type, hour, and category
The percentage contribution of each type to total sales
Exploratory Data Analysis
select distinct year(s.transaction_Date)
from dbo.CoffeeSales s
Min, Max and Average number of items per transaction
select max(s.transaction_qty) Max_Qty, min(s.transaction_qty) Min_Qty, avg(s.transaction_qty) Avg_Qty
from dbo.CoffeeSales s
Min, Max and average price
select max(s.unit_price) Max_Price, min(s.unit_price) Min_Price, avg(s.unit_price) Avg_Price
from dbo.CoffeeSales s
Sales by store location
select sto.store_location ,round(sum(s.unit_price),2) Sum_of_Sales
from dbo.CoffeeSales s
left join dbo.stores sto on sto.store_Id = s.store_Id
group by sto.store_location
order by 2 desc
Sales amount by items in the transaction
select s.transaction_qty ,round(sum(s.unit_price),2) Sum_of_Sales
from dbo.CoffeeSales s
group by s.transaction_qty
order by 2 desc
Number of the items per transaction quantity
select s.transaction_qty ,count(s.transaction_id) Count_of_Items
from dbo.CoffeeSales s
group by s.transaction_qty
order by 2 desc
Top 5 number of items per category
select c.product_category ,count( s.transaction_id) Count_of_trans
from dbo.CoffeeSales s
left join dbo.Categories c on c.categoryId = s.categoryId
group by c.product_category
order by 2 desc
offset 0 rows FETCH next 5 ROWS ONLY;
Number of transactions per hours
select c.product_category , cast( sum(s.unit_price) as decimal(10,2)) Sum_By_Category
from dbo.CoffeeSales s
left join dbo.Categories c on c.categoryId = s.categoryId
group by c.product_category
order by 2 desc
offset 0 rows FETCH next 5 ROWS ONLY;
Number of items per category per type
select v.product_category,v.product_type ,v.Count_of_trans
, rank( ) over ( partition by v.product_category order by Count_of_trans desc) Rnk from (
select c.product_category, pt.product_type
,count(s.transaction_id) Count_of_trans
from dbo.CoffeeSales s
left join dbo.Categories c on c.categoryId = s.categoryId
left join dbo.product_type pt on pt.product_type_Id = s.product_typeId
group by c.product_category, pt.product_type
)v order by 1
Number of items per type per hours
select* from(
select * , rank( ) over ( partition by v.product_category order by Count_of_trans desc) Rnk
from (
select c.product_category, pt.product_type , DATEPART(HOUR,s.transaction_time) hourNo
,count(s.transaction_id) Count_of_trans
from dbo.CoffeeSales s
left join dbo.Categories c on c.categoryId = s.categoryId
left join dbo.product_type pt on pt.product_type_Id = s.product_typeId
group by c.product_category, pt.product_type, DATEPART(HOUR,s.transaction_time)
)v )vv where vv.Rnk=1
The percentage of sales per category
select* , cast((ss.sub_sum / ss.total) as decimal(10,2)) Percent_of_Sales
from (
select c.product_category
, sum( s.unit_price) sub_sum
,(select sum(s.unit_price) from dbo.CoffeeSales s ) total
from dbo.CoffeeSales s
left join dbo.Categories c on c.categoryId = s.categoryId
group by c.product_category
)ss
