Analytical and Statistical Queries

Daily Revenue

SELECT 
    DATE(timestamp) AS payment_date, 
    SUM(amount) AS total_revenue
FROM payments
GROUP BY DATE(timestamp)
ORDER BY payment_date;

Daily Order Count and Average Order Value

SELECT 
    DATE(o.datetime) AS order_date, 
    COUNT(o.id) AS total_orders, 
    AVG(p.amount) AS avg_order_value
FROM orders o
JOIN payments p ON o.id = p.order_id
GROUP BY DATE(o.datetime)
ORDER BY order_date;

Order Distribution by Order Category

SELECT
    c.name AS category,
    COUNT(oi.id) AS total_items_ordered
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY c.name
ORDER BY total_items_ordered DESC;

Top 10 Best-Selling Products

SELECT
    p.name AS product,
    SUM(oi.quantity) AS total_quantity_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.name
ORDER BY total_quantity_sold DESC
LIMIT 10;

Online Orders vs. Tab Orders Count

SELECT 'Online Orders' AS order_type, COUNT(*) AS total_orders
FROM online_orders
UNION ALL
SELECT 'Tab Orders' AS order_type, COUNT(*) AS total_orders
FROM tab_orders;

Revenue Split: Online vs Tab Orders

SELECT
    'Online Orders' AS order_type,
    SUM(p.amount) AS total_revenue
FROM orders o
JOIN payments p ON o.id = p.order_id
JOIN online_orders oo ON o.id = oo.order_id;

SELECT
    'Tab Orders' AS order_type,
    SUM(p.amount) AS total_revenue
FROM orders o
JOIN payments p ON o.id = p.order_id
JOIN tab_orders tord ON o.id = tord.order_id;

Average Shift Duration per Manager

SELECT
    manager_id,
    AVG(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) AS avg_shift_duration_hours
FROM shifts
GROUP BY manager_id;

Orders Managed by Each Front Staff

SELECT
    fs.employee_id,
    COUNT(tord.order_id) AS total_orders_managed
FROM tab_orders tord
JOIN front_staff fs ON tord.front_staff_id = fs.employee_id
GROUP BY fs.employee_id
ORDER BY total_orders_managed DESC;

Average Tip Percentage per Staff Role

SELECT
    sr.name AS staff_role,
    AVG(fs.tip_percent) AS avg_tip_percent
FROM front_staff fs
JOIN staff_roles sr ON fs.staff_role_id = sr.id
GROUP BY sr.name;

Inventory Turnover Ratio per Product

SELECT
    p.name AS product,
    inv.quantity AS current_inventory,
    SUM(oi.quantity) AS total_quantity_ordered,
    CASE
        WHEN inv.quantity = 0 THEN NULL
        ELSE SUM(oi.quantity)::decimal / inv.quantity
    END AS turnover_ratio
FROM products p
JOIN inventories inv ON p.id = inv.product_id
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.name, inv.quantity
ORDER BY turnover_ratio DESC;

Most Popular Table by Reservations

SELECT
    table_number,
    COUNT(*) AS reservation_count
FROM frontstaff_managed_reservations
GROUP BY table_number
ORDER BY reservation_count DESC
LIMIT 1;

Reservations by Day of Week

SELECT
    EXTRACT(DOW FROM datetime) AS day_of_week,  -- 0=Sunday, 1=Monday, etc.
    COUNT(*) AS total_reservations
FROM reservations
GROUP BY day_of_week
ORDER BY total_reservations DESC;