Data Structures and Algorithms

CREATE DATABASE grocery_delivery_db;
USE grocery_delivery_db;

SELECT SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'Delivered';

SELECT p.product_name, SUM(oi.quantity) AS units_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status = 'Delivered'
GROUP BY p.product_name
ORDER BY units_sold DESC
LIMIT 10;

WITH order_values AS (
    SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'Delivered'
    GROUP BY o.order_id
)
SELECT ROUND(AVG(order_value), 2) AS average_order_value FROM order_values;

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
WHERE order_status = 'Delivered'
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

SELECT s.store_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM stores s
JOIN orders o ON s.store_id = o.store_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'Delivered'
GROUP BY s.store_name
ORDER BY revenue DESC;

SELECT ROUND(100.0 * SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancellation_rate
FROM orders;

SELECT EXTRACT(HOUR FROM order_date) AS order_hour, COUNT(*) AS total_orders
FROM orders
WHERE order_status = 'Delivered'
GROUP BY EXTRACT(HOUR FROM order_date)
ORDER BY total_orders DESC;