This project provides an in-depth SQL analysis of a Pizza Hut database to extract valuable insights, such as order statistics, revenue generation, pizza popularity, and sales distribution. It consists of various SQL queries categorized into Basic, Intermediate, and Advanced levels to explore different aspects of the pizza sales data.
Stores details of orders placed.
CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE NOT NULL, order_time TIME NOT NULL );Stores details of individual pizzas ordered.
CREATE TABLE order_details ( order_details_id INT PRIMARY KEY, order_id INT NOT NULL, pizza_id TEXT NOT NULL, quantity INT NOT NULL );SELECT COUNT(order_id) AS total_orders FROM orders;SELECT ROUND(SUM(order_details.quantity * pizzas.price), 0) AS total_revenue FROM order_details JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id;SELECT pizza_types.name, MAX(pizzas.price) AS highest_priced_pizza FROM pizza_types JOIN pizzas ON pizza_types.pizza_type_id = pizzas.pizza_type_id GROUP BY pizza_types.name ORDER BY highest_priced_pizza DESC LIMIT 1;SELECT pizzas.size, COUNT(order_details.order_details_id) AS most_common_pizza_size_ordered FROM pizzas JOIN order_details ON pizzas.pizza_id = order_details.pizza_id GROUP BY pizzas.size ORDER BY most_common_pizza_size_ordered DESC LIMIT 1;SELECT pizza_types.name, SUM(order_details.quantity) AS most_ordered_pizza 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 most_ordered_pizza DESC LIMIT 5;SELECT pizza_types.category, SUM(order_details.quantity) AS 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.category;SELECT HOUR(orders.order_time) AS order_hour, COUNT(order_id) AS total_orders FROM orders GROUP BY HOUR(orders.order_time);SELECT category, COUNT(name) AS pizza_count FROM pizza_types GROUP BY category;SELECT ROUND(AVG(quantity), 0) AS avg_pizzas_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;SELECT pizza_types.name, ROUND(SUM(order_details.quantity * pizzas.price), 0) AS total_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.name ORDER BY total_revenue DESC LIMIT 3;SELECT pizza_types.name, ROUND(SUM(order_details.quantity * pizzas.price) / (SELECT ROUND(SUM(order_details.quantity * pizzas.price), 2) FROM order_details JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id) * 100, 2) AS revenue_percentage 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;SELECT order_date, SUM(total_revenue) OVER (ORDER BY order_date) AS cumulative_revenue FROM ( SELECT orders.order_date, ROUND(SUM(order_details.quantity * pizzas.price), 0) AS total_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 total_sales;WITH ranked_pizza_types AS ( SELECT pizza_types.name, pizza_types.category, SUM(order_details.quantity * pizzas.price) AS total_revenue, RANK() OVER (PARTITION BY pizza_types.category ORDER BY SUM(order_details.quantity * pizzas.price) DESC) AS rnk FROM order_details JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id JOIN pizza_types ON pizza_types.pizza_type_id = pizzas.pizza_type_id GROUP BY pizza_types.name, pizza_types.category ) SELECT name, category, total_revenue, rnk FROM ranked_pizza_types WHERE rnk <= 3;This project utilizes SQL queries to analyze pizza sales data effectively. By leveraging JOINs, GROUP BY, aggregate functions, window functions, and ranking, we uncover valuable insights that can help optimize sales and inventory management.
Project by: Ayonika Dutta