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;

Stored procedure

CREATE OR REPLACE FUNCTION get_analytics_stats(
    p_start_date DATE,
    p_end_date DATE
)
RETURNS JSON AS $$
DECLARE
    result JSON;
BEGIN
    SELECT json_build_object(
        'dailyRevenue', (
            SELECT json_agg(r)
            FROM (
                SELECT DATE(timestamp) AS payment_date, SUM(amount) AS total_revenue
                FROM payments
                WHERE DATE(timestamp) BETWEEN p_start_date AND p_end_date
                GROUP BY DATE(timestamp)
                ORDER BY payment_date
            ) r
        ),
        'dailyOrders', (
            SELECT json_agg(r)
            FROM (
                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
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
                GROUP BY DATE(o.datetime)
                ORDER BY order_date
            ) r
        ),
        'orderDistribution', (
            SELECT json_agg(r)
            FROM (
                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
                JOIN orders o ON o.id = oi.order_id  -- filter by order date
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
                GROUP BY c.name
                ORDER BY total_items_ordered DESC
            ) r
        ),
        'topProducts', (
            SELECT json_agg(r)
            FROM (
                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
                JOIN orders o ON o.id = oi.order_id  -- apply date filter
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
                GROUP BY p.name
                ORDER BY total_quantity_sold DESC
                LIMIT 10
            ) r
        ),
        'onlineVsTabOrders', (
            SELECT json_agg(r)
            FROM (
                SELECT 'Online Orders' AS order_type, COUNT(*) AS total_orders
                FROM online_orders oo
                JOIN orders o ON oo.order_id = o.id
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
                UNION ALL
                SELECT 'Tab Orders' AS order_type, COUNT(*) AS total_orders
                FROM tab_orders tord
                JOIN orders o ON tord.order_id = o.id
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
            ) r
        ),
        'revenueSplit', (
            SELECT json_agg(r)
            FROM (
                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
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
                UNION ALL
                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
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
            ) r
        ),
        'avgShiftDuration', (
            SELECT json_agg(r)
            FROM (
                SELECT manager_id,
                       AVG(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) AS avg_shift_duration_hours
                FROM shifts
                WHERE DATE(start_time) BETWEEN p_start_date AND p_end_date
                GROUP BY manager_id
            ) r
        ),
        'ordersByFrontStaff', (
            SELECT json_agg(r)
            FROM (
                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
                JOIN orders o ON tord.order_id = o.id
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
                GROUP BY fs.employee_id
                ORDER BY total_orders_managed DESC
            ) r
        ),
        'avgTipPercentage', (
            SELECT json_agg(r)
            FROM (
                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
            ) r
        ),
        'inventoryTurnover', (
            SELECT json_agg(r)
            FROM (
                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
                JOIN orders o ON o.id = oi.order_id
                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
                GROUP BY p.name, inv.quantity
                ORDER BY turnover_ratio DESC
            ) r
        ),
        'mostPopularTable', (
            SELECT json_build_object(
                'table_number', table_number,
                'reservation_count', reservation_count
            )
            FROM (
                SELECT table_number, COUNT(*) AS reservation_count
                FROM frontstaff_managed_reservations
                WHERE DATE(datetime) BETWEEN p_start_date AND p_end_date
                GROUP BY table_number
                ORDER BY reservation_count DESC
                LIMIT 1
            ) sub
        ),
        'reservationsByDayOfWeek', (
            SELECT json_agg(r)
            FROM (
                SELECT EXTRACT(DOW FROM datetime) AS day_of_week, COUNT(*) AS total_reservations
                FROM reservations
                WHERE DATE(datetime) BETWEEN p_start_date AND p_end_date
                GROUP BY day_of_week
                ORDER BY total_reservations DESC
            ) r
        )
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT get_analytics_stats('2025-01-01', '2025-01-31');