= 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;\\
}}}