Pizza sales analysis.sql

CREATE DATABASE PIZZAHUT;
use PIZZAHUT;

select * from order_details
select * from orders
select * from pizza_types
select * from pizzas


------Retrieve the total number of orders placed.
select count(order_id) as total_orders from orders



-------Calculate the total revenue generated from pizza sales.
select SUM(order_details.quantity*pizzas.price) as total_revenue from order_details
inner join pizzas
on order_details.pizza_id= pizzas.pizza_id



-------Identify the highest-priced pizza.
select top 1 pizza_types.name,pizzas.price
from pizza_types inner join pizzas
on pizza_types.pizza_type_id= pizzas.pizza_type_id
order by pizzas.price desc



-------Identify the most common pizza size ordered.
select count(order_details.order_details_id) tot_qty, pizzas.size
from order_details inner join pizzas
on order_details.pizza_id=pizzas.pizza_id
group by pizzas.size
order by tot_qty desc






--------Join the necessary tables to find the total quantity of each pizza category ordered.
select pizza_types.category, sum(order_details.quantity) as tot_qty
from pizza_types inner join pizzas
on pizza_types.pizza_type_id=pizzas.pizza_type_id
inner join  order_details
on order_details.pizza_id=pizzas.pizza_id
group by pizza_types.category
order by tot_qty desc





--------Determine the distribution of orders by hour of the day.
select DATEPART(hour,time)  , COUNT(order_id) as tot_or from orders
group by DATEPART(hour,time) 
order by  tot_or desc;




-------Join relevant tables to find the category-wise distribution of pizzas.
select category,COUNT(name) from pizza_types
group by category




------Group the orders by date and calculate the average number of pizzas ordered per day.
select avg (tot_or) from 
(select orders.date , SUM(order_details.quantity) as tot_or
from orders inner join order_details
on orders.order_id=order_details.order_id
group by orders.date) as a




-------Determine the top 3 most ordered pizza types based on revenue.
select top 3 pizza_types.name,SUM(order_details.quantity*pizzas.price) as revenue
from pizza_types inner join pizzas
on pizza_types.pizza_type_id=pizzas.pizza_type_id
inner join order_details on 
order_details.pizza_id=pizzas.pizza_id
group by pizza_types.name
order by revenue desc



--------Calculate the percentage contribution of each pizza type to total revenue.
select pizza_types.category,
(SUM(order_details.quantity*pizzas.price) /( select SUM(order_details.quantity*pizzas.price) from 
order_details inner join pizzas
on order_details.pizza_id=pizzas.pizza_id)* 100) as revenue from
pizza_types inner join pizzas
on pizza_types.pizza_type_id=pizzas.pizza_type_id
inner join order_details
on order_details.pizza_id=pizzas.pizza_id
group by pizza_types.category
order by revenue desc 



------Determine the top 3 most ordered pizza types based on revenue for each pizza category.
select top 3 pizza_types.category, SUM(order_details.quantity*pizzas.price) as revenue
from pizza_types inner join pizzas
on pizza_types.pizza_type_id=pizzas.pizza_type_id
inner join order_details
on order_details.pizza_id=pizzas.pizza_id
group by pizza_types.category
order by revenue desc