Changes between Initial Version and Version 1 of Advanced Database Reports (SQL and Stored Procedures)

Timestamp:

08/27/25 08:51:58 (7 days ago)

Author:

221531

Comment:

--

Advanced Database Reports (SQL and Stored Procedures)

@@ +1 @@
+= Advanced Database Reports
+== Orders per Item–Category–Restaurant
+{{{#!td
+  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 m ON m.item_id = i.item_id\\
+JOIN restaurant r ON r.restaurant_id = m.restaurant_id\\
+WHERE 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
+{{{#!td
+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
+{{{#!td
+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
+{{{#!td
+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
+{{{#!td
+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) -- links delivery men to firm\\
+LEFT JOIN orders o ON o.user_id IS NOT NULL AND o.address_id IS NOT NULL\\
+                    AND o.order_id IN (SELECT order_id FROM order_items) -- ensures valid orders\\
+                    AND r.restaurant_id IN (SELECT restaurant_id \\
+                                             FROM menu m \\
+                                             JOIN item i ON i.item_id = m.item_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
+{{{#!td
+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 item i ON i.item_id = m.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
+{{{#!td
+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
+{{{#!td
+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.user_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;\\
+}}}