Assignment 2- Mohibbulla Munshi

cloud kitchen

এক্সাম উইক ২ এর এসাইনমেন্ট

Cloud Kitchen Activities and Analysis

User Order History: Retrieve the order history for a specific user, showing order details (order date, total amount, status) and the list of dishes in each order.

select
o.order_date,
o.total_amount,
o.status,
d.description
from
orders o
join order_items oi on o.id = oi.order_id
join dishes d on oi.dish_id = d.id
where
o.user_id = 2

Revenue by Cuisine: Calculate the total revenue for each cuisine based on completed orders.

SELECT
d.cuisine,
SUM(o.total_amount) as total_revenue
FROM
orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN dishes d ON oi.dish_id = d.id
WHERE
o.status = 'Completed'
GROUP BY
d.cuisine;

Popular Dish:: Identify the most ordered dish and display its details.

SELECT
d.name,
d.description,
SUM(oi.quantity) as total_quantity_ordered
FROM
order_items oi
JOIN dishes d ON oi.dish_id = d.id
GROUP BY
d.name, d.description
ORDER BY
total_quantity_ordered DESC
LIMIT 1;