= 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_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
{{{#!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)\\
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
{{{#!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 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
{{{#!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.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;\\

}}}