Answers.sql

-- 1. Retrieve the total number of orders placed.
select * from orders;

SELECT
    COUNT(order_id) AS total_orders
FROM
    orders;


-- 2. Calculate the total revenue generated from pizza sales.
select * from pizzas;

SELECT
    ROUND(SUM(order_details.quantity * pizzas.price),2) AS total_rev_pizza_sales
FROM
    order_details
        JOIN
    pizzas ON pizzas.pizza_id = order_details.pizza_id;


-- 3. Identify the highest-priced pizza.
SELECT
    pizza_types.name, pizzas.price
FROM
    pizza_types
        JOIN
    pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id
ORDER BY pizzas.price DESC
LIMIT 01;


-- 4. Identify the most common pizza size ordered.
SELECT
    pizzas.size, COUNT(order_details.order_id) AS order_count
FROM
    pizzas
        JOIN
    order_details ON pizzas.pizza_id = order_details.pizza_id
GROUP BY pizzas.size
ORDER BY order_count DESC;


-- 5. List the top 5 most ordered pizza types along with their quantities.
SELECT
    pizza_types.name, SUM(order_details.quantity) total_quantity
FROM
    pizza_types
        JOIN
    pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id
        JOIN
    order_details ON order_details.pizza_id = pizzas.pizza_id
GROUP BY pizza_types.name
ORDER BY total_quantity DESC
LIMIT 5;


-- 6. Join the necessary tables to find the total quantity of each pizza category ordered.
SELECT
    pizza_types.category,
    SUM(order_details.quantity) AS quantity
FROM
    pizza_types
        JOIN
    pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id
        JOIN
    order_details ON order_details.pizza_id = pizzas.pizza_id
GROUP BY pizza_types.category
ORDER BY quantity DESC;


-- 7. Determine the distribution of orders by hour of the day.
SELECT
    HOUR(order_time) AS orders_hr,
    COUNT(order_id) AS orders_count
FROM
    orders
GROUP BY HOUR(order_time);


-- 8. Join relevant tables to find the category-wise distribution of pizzas.
SELECT
    category, COUNT(name)
FROM
    pizza_types
GROUP BY category;


-- 9. Group the orders by date and calculate the average number of pizzas ordered per day.
select round(avg(quantity),0)  as avg_pizzas_orders_per_day from
(select orders.order_date,sum(order_details.quantity) as quantity
from orders
join order_details
on orders.order_id=order_details.order_id
group by orders.order_date)  as order_quantity;


-- 10. Determine the top 3 most ordered pizza types based on revenue.
SELECT pizza_types.name,
    ROUND(SUM(order_details.quantity * pizzas.price),2) AS total_rev_pizza_sales
FROM
    pizza_types
        JOIN
    pizzas ON pizzas.pizza_type_id = pizza_types.pizza_type_id
    join  order_details
    on order_details.pizza_id=pizzas.pizza_id
    group by pizza_types.name
    order by total_rev_pizza_sales desc
    limit 3;


-- 11. Calculate the percentage contribution of each pizza type to total revenue.
SELECT pizza_types.category,
    round((sum(order_details.quantity * pizzas.price) / (SELECT
    ROUND(SUM(order_details.quantity * pizzas.price),2) AS total_rev_pizza_sales
FROM
    order_details
        JOIN
    pizzas ON pizzas.pizza_id = order_details.pizza_id
))* 100,2 )AS total_rev_pizza_sales
FROM
    pizza_types
        JOIN
    pizzas ON pizzas.pizza_type_id = pizza_types.pizza_type_id
    join  order_details
    on order_details.pizza_id=pizzas.pizza_id
    group by pizza_types.category order by total_rev_pizza_sales desc ;


-- 12. Analyze the cumulative revenue generated over time.
select order_date,sum(revenue)  over( order by order_date) as cum_revenue
from
(select orders.order_date,sum(order_details.quantity*pizzas.price) as revenue
from order_details
join pizzas
on order_details.pizza_id= pizzas.pizza_id
join orders
on orders.order_id =order_details.order_id
group by orders.order_date) as sales ;


-- 13. Determine the top 3 most ordered pizza types based on revenue for each pizza category.
select name ,revenue from
(select category, name ,revenue, rank() over (partition by category  order by revenue desc) as rn
from
(select pizza_types.category,pizza_types.name,
sum(order_details.quantity*pizzas.price) as revenue
from pizza_types
join pizzas
on pizza_types.pizza_type_id =pizzas.pizza_type_id
join order_details
on order_details.pizza_id =pizzas.pizza_id
group by pizza_types.category,pizza_types.name) as a) as b
where rn <=3;