Advanced Database Reports

Orders per Item–Category–Restaurant

SELECT r.name AS restaurant_name, c.name AS category_name, i.name AS item_name, SUM(oi.quantity) AS total_quantity, SUM(oi.total_price) AS total_revenue FROM ORDERS o JOIN ORDER_ITEMS oi ON oi.order_id = o.order_id JOIN item i ON i.item_id = oi.item_id JOIN item_category ic ON ic.item_id = i.item_id JOIN category c ON c.category_id = ic.category_id JOIN MENU_ITEM mi ON mi.item_id = i.item_id JOIN MENU m ON m.menu_id = mi.menu_id JOIN restaurant r ON r.restaurant_id = m.restaurant_id WHERE o.order_status IS NOT NULL AND o.order_date >= CURRENT_DATE - INTERVAL '3 months' GROUP BY r.name, c.name, i.name ORDER BY r.name, category_name, total_quantity DESC;

Monthly Restaurant Earnings vs Costs vs Net Profit

SELECT r.name AS restaurant_name, TO_CHAR(e.date, 'YYYY-MM') AS year_month, ROUND(SUM(e.amount), 2) AS total_earnings, ROUND(COALESCE(SUM(c.amount), 0), 2) AS total_costs, ROUND(SUM(e.amount) - COALESCE(SUM(c.amount), 0), 2) AS net_profit FROM restaurant r LEFT JOIN earnings e ON e.restaurant_id = r.restaurant_id LEFT JOIN costs c ON c.restaurant_id = r.restaurant_id AND TO_CHAR(c.date, 'YYYY-MM') = TO_CHAR(e.date, 'YYYY-MM') WHERE e.date >= CURRENT_DATE - INTERVAL '6 months' GROUP BY r.name, year_month ORDER BY r.name, year_month DESC;

Customer Spending Trends by Month

SELECT u.user_id, u.first_name ' ' u.last_name AS customer_name, TO_CHAR(o.order_date, 'YYYY-MM') AS year_month, COUNT(DISTINCT o.order_id) AS num_orders, SUM(oi.total_price) AS total_spent FROM app_user u JOIN ORDERS o ON o.user_id = u.user_id JOIN ORDER_ITEMS oi ON oi.order_id = o.order_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '3 months' GROUP BY u.user_id, customer_name, year_month ORDER BY u.user_id, year_month DESC;

Popular Categories by Orders

SELECT c.name AS category_name, TO_CHAR(o.order_date, 'YYYY-MM') AS year_month, SUM(oi.quantity) AS total_quantity, SUM(oi.total_price) AS revenue FROM category c JOIN item_category ic ON ic.category_id = c.category_id JOIN item i ON i.item_id = ic.item_id JOIN ORDER_ITEMS oi ON oi.item_id = i.item_id JOIN ORDERS o ON o.order_id = oi.order_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '12 months' GROUP BY c.name, year_month ORDER BY year_month DESC, revenue DESC;

Restaurant Summary: orders, earnings, costs, employees

SELECT r.restaurant_id, r.name AS restaurant_name, COUNT(DISTINCT ro.user_id) + COUNT(DISTINCT da.user_id) AS num_employees, COUNT(DISTINCT o.order_id) AS total_orders, COALESCE(SUM(er.amount), 0) AS total_earnings, COALESCE(SUM(c.amount), 0) AS total_costs, COALESCE(SUM(er.amount), 0) - COALESCE(SUM(c.amount), 0) AS net_profit FROM restaurant r LEFT JOIN restaurant_owners ro ON ro.restaurant_id = r.restaurant_id LEFT JOIN delivery_assignment da ON da.deliveryfirm_id IN (SELECT deliveryfirm_id FROM delivery_firm) LEFT JOIN ORDERS o ON o.restaurant_id = r.restaurant_id LEFT JOIN earnings er ON er.restaurant_id = r.restaurant_id LEFT JOIN costs c ON c.restaurant_id = r.restaurant_id GROUP BY r.restaurant_id, r.name;

Top-Selling Items Per Restaurant

SELECT r.restaurant_id, r.name AS restaurant_name, i.item_id, i.name AS item_name, SUM(oi.quantity) AS total_sold, SUM(oi.total_price) AS revenue FROM restaurant r JOIN MENU m ON m.restaurant_id = r.restaurant_id JOIN MENU_ITEM mi ON mi.menu_id = m.menu_id JOIN item i ON i.item_id = mi.item_id JOIN ORDER_ITEMS oi ON oi.item_id = i.item_id JOIN ORDERS o ON o.order_id = oi.order_id GROUP BY r.restaurant_id, r.name, i.item_id, i.name ORDER BY r.restaurant_id, revenue DESC;

Repeat vs. New Customers

SELECT CASE WHEN order_count = 1 THEN 'One-time Customers' ELSE 'Repeat Customers' END AS customer_type, COUNT(*) AS num_customers, SUM(total_spent) AS total_revenue FROM ( SELECT u.user_id, COUNT(o.order_id) AS order_count, SUM(oi.total_price) AS total_spent FROM app_user u JOIN ORDERS o ON o.user_id = u.user_id JOIN ORDER_ITEMS oi ON oi.order_id = o.order_id GROUP BY u.user_id ) sub GROUP BY customer_type;

Delivery Firm Performance

SELECT df.deliveryfirm_id, df.name AS delivery_firm, COUNT(DISTINCT o.order_id) AS total_orders, ROUND(SUM(oi.total_price),2) AS total_order_value FROM delivery_firm df JOIN delivery_assignment da ON da.deliveryfirm_id = df.deliveryfirm_id JOIN ORDERS o ON o.deliveryman_id = da.user_id JOIN ORDER_ITEMS oi ON oi.order_id = o.order_id GROUP BY df.deliveryfirm_id, df.name ORDER BY total_orders DESC;