UPDATE customer_orders
SET exclusions = 'Not Available'
WHERE exclusions IS NULL OR exclusions = '';
UPDATE customer_orders
SET extras = 'Not Available'
WHERE extras IS NULL OR extras = '';
UPDATE customer_orders
SET extras = 'Not Available'
WHERE extras = 'null';
UPDATE customer_orders
SET exclusions = 'Not Available'
WHERE exclusions = 'null';
UPDATE runner_orders
SET cancellation = 'Not Available'
WHERE cancellation IS NULL OR cancellation = '';
UPDATE runner_orders
SET duration = 'Not Available'
WHERE duration = 'null' ;
UPDATE runner_orders
SET distance = 'Not Available'
WHERE distance = 'null' ;
UPDATE runner_orders
SET pickup_time = 'Not Available'
WHERE pickup_time = 'null' ;
UPDATE runner_orders
SET cancellation = 'Not Available'
WHERE cancellation = 'null' ;
select * from customer_orders
select * from runner_orders;
select * from pizza_names;
select * from pizza_recipes;
select * from pizza_toppings;
select * from runners;
1]How many pizzas were ordered?
Select count(*) total_orders
from customer_orders;
2]How many unique customer orders were made?
Select count(distinct customer_id) unique_customers
from customer_orders;
3]How many successful orders were delivered by each runner?
select runner_id,COUNT(order_id) total_sucessful_orders
from runner_orders
where cancellation = 'Not Available'
group by runner_id
4]How many of each type of pizza was delivered?
select p.pizza_name,count(c.pizza_id) pizzas_delieverd
from customer_orders c
join runner_orders r
on c.order_id = r.order_id
join pizza_names p
on p.pizza_id =c.pizza_id
where cancellation = 'Not Available'
group by p.pizza_name
5]How many Vegetarian and Meatlovers were ordered by each customer?
select c.customer_id,p.pizza_name,count(*) pizzas_ordered
from customer_orders c
join runner_orders r
on c.order_id = r.order_id
join pizza_names p
on p.pizza_id =c.pizza_id
where cancellation = 'Not Available'
group by p.pizza_name,c.customer_id
6]What was the maximum number of pizzas delivered in a single order?
select MAX(max_orders) max_pizzas_delievered
from(
select c.order_id,COUNT(*) Max_orders
from customer_orders c
join runner_orders r
on c.order_id=r.order_id
where [Link] ='Not Available'
group by c.order_id
) order_counts
7]For each customer, how many delivered pizzas had at least 1 change and how many had
no changes?
select c.customer_id ,count(*) total_orders,
count(
case when exclusions = 'Not Available' and
extras = 'Not Available'
then c.order_id else Null end ) no_changes,
count(
case when exclusions <> 'Not Available' or
extras <> 'Not Available'
then c.order_id else Null end ) changes
from customer_orders c
join runner_orders r
on c.order_id = r.order_id
group by c.customer_id
8]How many pizzas were delivered that had both exclusions and extras?
select c.customer_id ,count(*) total_orders,
count(
case when exclusions <> 'Not Available' AND
extras <> 'Not Available'
then c.order_id else Null end ) changes
from customer_orders c
join runner_orders r
on c.order_id = r.order_id
group by c.customer_id
9]What was the total volume of pizzas ordered for each hour of the day?
select sum(pizza_id) total_volume,datepart(hour,order_time) order_hour
from customer_orders
group by datepart(hour,order_time)
order by order_hour
10]What was the volume of orders for each day of the week?
SELECT
cast(order_time as date) ordered_date,
datename(weekday, order_time) order_day,
count(*) total_pizzas_ordered
from customer_orders
where order_time IS NOT NULL
group by
cast(order_time as date),
datename(WEEKDAY, order_time),
datepart(WEEKDAY, order_time)
ORDER BY
cast(order_time as date);