Changes between Version 1 and Version 2 of Advanced Database Reports (SQL and Stored Procedures)
- Timestamp:
- 08/28/25 22:57:12 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Advanced Database Reports (SQL and Stored Procedures)
v1 v2 2 2 == Orders per Item–Category–Restaurant 3 3 {{{#!td 4 SELECT 4 SELECT 5 5 r.name AS restaurant_name, 6 6 c.name AS category_name, … … 8 8 SUM(oi.quantity) AS total_quantity, 9 9 SUM(oi.total_price) AS total_revenue 10 FROM orderso\\11 JOIN order_itemsoi ON oi.order_id = o.order_id\\10 FROM ORDERS o\\ 11 JOIN ORDER_ITEMS oi ON oi.order_id = o.order_id\\ 12 12 JOIN item i ON i.item_id = oi.item_id\\ 13 13 JOIN item_category ic ON ic.item_id = i.item_id\\ 14 14 JOIN category c ON c.category_id = ic.category_id\\ 15 JOIN menu m ON m.item_id = i.item_id\\ 15 JOIN MENU_ITEM mi ON mi.item_id = i.item_id\\ 16 JOIN MENU m ON m.menu_id = mi.menu_id\\ 16 17 JOIN restaurant r ON r.restaurant_id = m.restaurant_id\\ 17 WHERE o.order_date >= CURRENT_DATE - INTERVAL '3 months'\\ 18 WHERE o.order_status IS NOT NULL\\ 19 AND o.order_date >= CURRENT_DATE - INTERVAL '3 months'\\ 18 20 GROUP BY r.name, c.name, i.name\\ 19 21 ORDER BY r.name, category_name, total_quantity DESC;\\ … … 22 24 == Monthly Restaurant Earnings vs Costs vs Net Profit 23 25 {{{#!td 24 SELECT r.name AS restaurant_name, 25 TO_CHAR(e.date, 'YYYY-MM') AS year_month,\\ 26 ROUND(SUM(e.amount), 2) AS total_earnings,\\ 27 ROUND(COALESCE(SUM(c.amount), 0), 2) AS total_costs,\\ 28 ROUND(SUM(e.amount) - COALESCE(SUM(c.amount), 0), 2) AS net_profit\\ 26 SELECT 27 r.name AS restaurant_name, 28 TO_CHAR(e.date, 'YYYY-MM') AS year_month, 29 ROUND(SUM(e.amount), 2) AS total_earnings, 30 ROUND(COALESCE(SUM(c.amount), 0), 2) AS total_costs, 31 ROUND(SUM(e.amount) - COALESCE(SUM(c.amount), 0), 2) AS net_profit 29 32 FROM restaurant r\\ 30 33 LEFT JOIN earnings e ON e.restaurant_id = r.restaurant_id\\ 31 34 LEFT JOIN costs c ON c.restaurant_id = r.restaurant_id\\ 32 35 AND TO_CHAR(c.date, 'YYYY-MM') = TO_CHAR(e.date, 'YYYY-MM')\\ 33 36 WHERE e.date >= CURRENT_DATE - INTERVAL '6 months'\\ 34 37 GROUP BY r.name, year_month\\ … … 37 40 == Customer Spending Trends by Month 38 41 {{{#!td 39 SELECT u.user_id, u.first_name || ' ' || u.last_name AS customer_name,\\ 40 TO_CHAR(o.order_date, 'YYYY-MM') AS year_month,\\ 41 COUNT(DISTINCT o.order_id) AS num_orders,\\ 42 SUM(oi.total_price) AS total_spent\\ 42 SELECT 43 u.user_id, 44 u.first_name || ' ' || u.last_name AS customer_name, 45 TO_CHAR(o.order_date, 'YYYY-MM') AS year_month, 46 COUNT(DISTINCT o.order_id) AS num_orders, 47 SUM(oi.total_price) AS total_spent 43 48 FROM app_user u\\ 44 JOIN orderso ON o.user_id = u.user_id\\45 JOIN order_itemsoi ON oi.order_id = o.order_id\\49 JOIN ORDERS o ON o.user_id = u.user_id\\ 50 JOIN ORDER_ITEMS oi ON oi.order_id = o.order_id\\ 46 51 WHERE o.order_date >= CURRENT_DATE - INTERVAL '3 months'\\ 47 52 GROUP BY u.user_id, customer_name, year_month\\ … … 50 55 == Popular Categories by Orders 51 56 {{{#!td 52 SELECT c.name AS category_name, 53 TO_CHAR(o.order_date, 'YYYY-MM') AS year_month,\\ 54 SUM(oi.quantity) AS total_quantity,\\ 55 SUM(oi.total_price) AS revenue\\ 57 SELECT 58 c.name AS category_name, 59 TO_CHAR(o.order_date, 'YYYY-MM') AS year_month, 60 SUM(oi.quantity) AS total_quantity, 61 SUM(oi.total_price) AS revenue 56 62 FROM category c\\ 57 63 JOIN item_category ic ON ic.category_id = c.category_id\\ 58 64 JOIN item i ON i.item_id = ic.item_id\\ 59 JOIN order_itemsoi ON oi.item_id = i.item_id\\60 JOIN orderso ON o.order_id = oi.order_id\\65 JOIN ORDER_ITEMS oi ON oi.item_id = i.item_id\\ 66 JOIN ORDERS o ON o.order_id = oi.order_id\\ 61 67 WHERE o.order_date >= CURRENT_DATE - INTERVAL '12 months'\\ 62 68 GROUP BY c.name, year_month\\ … … 65 71 == Restaurant Summary: orders, earnings, costs, employees 66 72 {{{#!td 67 SELECT r.restaurant_id,68 r.name AS restaurant_name,\\69 COUNT(DISTINCT ro.user_id) \\70 + COUNT(DISTINCT da.user_id) AS num_employees,\\71 COUNT(DISTINCT o.order_id) AS total_orders,\\72 COALESCE(SUM(er.amount), 0) AS total_earnings,\\73 COALESCE(SUM(c.amount), 0) AS total_costs,\\74 COALESCE(SUM(er.amount), 0) - COALESCE(SUM(c.amount), 0) AS net_profit\\73 SELECT 74 r.restaurant_id, 75 r.name AS restaurant_name, 76 COUNT(DISTINCT ro.user_id) + COUNT(DISTINCT da.user_id) AS num_employees, 77 COUNT(DISTINCT o.order_id) AS total_orders, 78 COALESCE(SUM(er.amount), 0) AS total_earnings, 79 COALESCE(SUM(c.amount), 0) AS total_costs, 80 COALESCE(SUM(er.amount), 0) - COALESCE(SUM(c.amount), 0) AS net_profit 75 81 FROM restaurant r\\ 76 82 LEFT JOIN restaurant_owners ro ON ro.restaurant_id = r.restaurant_id\\ 77 LEFT JOIN delivery_assignment da ON da.deliveryfirm_id IN \\ 78 (SELECT deliveryfirm_id FROM delivery_firm) -- links delivery men to firm\\ 79 LEFT JOIN orders o ON o.user_id IS NOT NULL AND o.address_id IS NOT NULL\\ 80 AND o.order_id IN (SELECT order_id FROM order_items) -- ensures valid orders\\ 81 AND r.restaurant_id IN (SELECT restaurant_id \\ 82 FROM menu m \\ 83 JOIN item i ON i.item_id = m.item_id)\\ 83 LEFT JOIN delivery_assignment da ON da.deliveryfirm_id IN (SELECT deliveryfirm_id FROM delivery_firm)\\ 84 LEFT JOIN ORDERS o ON o.restaurant_id = r.restaurant_id\\ 84 85 LEFT JOIN earnings er ON er.restaurant_id = r.restaurant_id\\ 85 86 LEFT JOIN costs c ON c.restaurant_id = r.restaurant_id\\ … … 88 89 == Top-Selling Items Per Restaurant 89 90 {{{#!td 90 SELECT r.restaurant_id, 91 r.name AS restaurant_name,\\ 92 i.item_id,\\ 93 i.name AS item_name,\\ 94 SUM(oi.quantity) AS total_sold,\\ 95 SUM(oi.total_price) AS revenue\\ 91 SELECT 92 r.restaurant_id, 93 r.name AS restaurant_name, 94 i.item_id, 95 i.name AS item_name, 96 SUM(oi.quantity) AS total_sold, 97 SUM(oi.total_price) AS revenue 96 98 FROM restaurant r\\ 97 JOIN menu m ON m.restaurant_id = r.restaurant_id\\ 98 JOIN item i ON i.item_id = m.item_id\\ 99 JOIN order_items oi ON oi.item_id = i.item_id\\ 100 JOIN orders o ON o.order_id = oi.order_id\\ 99 JOIN MENU m ON m.restaurant_id = r.restaurant_id\\ 100 JOIN MENU_ITEM mi ON mi.menu_id = m.menu_id\\ 101 JOIN item i ON i.item_id = mi.item_id\\ 102 JOIN ORDER_ITEMS oi ON oi.item_id = i.item_id\\ 103 JOIN ORDERS o ON o.order_id = oi.order_id\\ 101 104 GROUP BY r.restaurant_id, r.name, i.item_id, i.name\\ 102 105 ORDER BY r.restaurant_id, revenue DESC;\\ … … 104 107 == Repeat vs. New Customers 105 108 {{{#!td 106 SELECT CASE 107 WHEN order_count = 1 THEN 'One-time Customers'\\ 108 ELSE 'Repeat Customers'\\ 109 END AS customer_type,\\ 110 COUNT(*) AS num_customers,\\ 111 SUM(total_spent) AS total_revenue\\ 109 SELECT 110 CASE WHEN order_count = 1 THEN 'One-time Customers' ELSE 'Repeat Customers' END AS customer_type, 111 COUNT(*) AS num_customers, 112 SUM(total_spent) AS total_revenue 112 113 FROM (\\ 113 114 SELECT u.user_id,\\ … … 115 116 SUM(oi.total_price) AS total_spent\\ 116 117 FROM app_user u\\ 117 JOIN orderso ON o.user_id = u.user_id\\118 JOIN order_itemsoi ON oi.order_id = o.order_id\\118 JOIN ORDERS o ON o.user_id = u.user_id\\ 119 JOIN ORDER_ITEMS oi ON oi.order_id = o.order_id\\ 119 120 GROUP BY u.user_id\\ 120 121 ) sub\\ … … 123 124 == Delivery Firm Performance 124 125 {{{#!td 125 SELECT df.deliveryfirm_id, 126 df.name AS delivery_firm,\\ 127 COUNT(DISTINCT o.order_id) AS total_orders,\\ 128 ROUND(SUM(oi.total_price),2) AS total_order_value\\ 126 SELECT 127 df.deliveryfirm_id, 128 df.name AS delivery_firm, 129 COUNT(DISTINCT o.order_id) AS total_orders, 130 ROUND(SUM(oi.total_price),2) AS total_order_value 129 131 FROM delivery_firm df\\ 130 132 JOIN delivery_assignment da ON da.deliveryfirm_id = df.deliveryfirm_id\\ 131 JOIN orders o ON o.user_id = da.user_id\\132 JOIN order_itemsoi ON oi.order_id = o.order_id\\133 JOIN ORDERS o ON o.deliveryman_id = da.user_id\\ 134 JOIN ORDER_ITEMS oi ON oi.order_id = o.order_id\\ 133 135 GROUP BY df.deliveryfirm_id, df.name\\ 134 136 ORDER BY total_orders DESC;\\ 137 135 138 }}}
