Analytical and Statistical Queries

Monthly operations summary

SELECT
    dates.operation_date,
    COUNT(DISTINCT r.id) as total_reservations,
    COUNT(DISTINCT o.id) as total_orders,
    COUNT(DISTINCT r.user_id) as unique_customers,
    COUNT(DISTINCT fs.employee_id) as active_employees,
    COALESCE(SUM(oi.quantity * oi.price), 0) as daily_revenue
FROM generate_series(
                         CURRENT_DATE - INTERVAL '30 days',
                         CURRENT_DATE,
                         '1 day'::interval
         ) dates(operation_date)
         LEFT JOIN reservations r
                   ON DATE(r.datetime) = dates.operation_date
    LEFT JOIN orders o
ON DATE(o.datetime) = dates.operation_date
    LEFT JOIN order_items oi
    ON o.id = oi.order_id
    LEFT JOIN tab_orders to2
    ON o.id = to2.order_id
    LEFT JOIN front_staff fs
    ON to2.front_staff_id = fs.employee_id
GROUP BY dates.operation_date
ORDER BY dates.operation_date DESC;

Revenue Split: Online vs. Tab Orders

CREATE OR REPLACE FUNCTION get_revenue_split(p_start_date DATE, p_end_date DATE)
RETURNS TABLE(order_type TEXT, total_revenue NUMERIC(14,2)) AS $$
BEGIN
RETURN QUERY
SELECT 'Online Orders'::text AS order_type,
        COALESCE(SUM(p.amount), 0)::numeric(14,2) 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 o.datetime::date BETWEEN p_start_date AND p_end_date

UNION ALL

SELECT 'Tab Orders'::text AS order_type,
        COALESCE(SUM(p.amount), 0)::numeric(14,2) 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 o.datetime::date BETWEEN p_start_date AND p_end_date;
END;
$$ LANGUAGE plpgsql;

Top 10 products by revenue

SELECT
    p.id as product_id,
    p.name as product_name,
    c.name as category_name,
    SUM(oi.quantity) as total_quantity_sold,
    SUM(oi.quantity * oi.price) as total_revenue,
    ROUND(100.0 * SUM(oi.quantity * oi.price) / SUM(SUM(oi.quantity * oi.price)) OVER (), 2) as revenue_share_percent
FROM products p
         JOIN categories c ON p.category_id = c.id
         JOIN order_items oi ON p.id = oi.product_id
         JOIN orders o ON o.id = oi.order_id
WHERE o.datetime >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY p.id, p.name, c.name
ORDER BY total_revenue DESC
    LIMIT 10;

Revenue by shift period

CREATE OR REPLACE VIEW v_revenue_by_shift_period AS
WITH distinct_shift_periods AS (
    SELECT DISTINCT
        start_time::time AS start_t,
        end_time::time AS end_t
    FROM
        shifts
)
SELECT
    TO_CHAR(o.datetime, 'YYYY-MM') AS period,
    dsp.start_t::text || '-' || dsp.end_t::text AS shift_period,
    SUM(oi.price * oi.quantity) AS total_revenue
FROM
    orders o
        JOIN
    order_items oi ON o.id = oi.order_id
        JOIN
    distinct_shift_periods dsp ON o.datetime::time >= dsp.start_t AND o.datetime::time < dsp.end_t
GROUP BY
    period,
    shift_period
ORDER BY
    period DESC,
    shift_period ASC;

Managers' shifts above monthly average revenue

WITH manager_worked_shifts AS (
    SELECT DISTINCT
        s.id AS shift_id,
        s.date,
        s.start_time,
        s.end_time,
        a.employee_id AS manager_id
    FROM assignments a
             JOIN shifts s   ON s.id = a.shift_id
             JOIN managers m ON m.employee_id = a.employee_id
    WHERE s.date >= date_trunc('year', CURRENT_DATE)::date
    AND s.date <  (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')::date
    ),
    shift_revenue AS (
SELECT
    mws.shift_id,
    date_trunc('month', mws.date)::date AS month_start,
    mws.date AS shift_date,
    mws.start_time,
    mws.end_time,
    mws.manager_id,
    COALESCE(SUM(oi.quantity * oi.price), 0)::numeric(14,2) AS shift_revenue
FROM manager_worked_shifts mws
    LEFT JOIN orders o
ON o.datetime::date = mws.date
    AND o.datetime::time >= mws.start_time
    AND o.datetime::time <  mws.end_time
    LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY
    mws.shift_id, month_start, mws.date, mws.start_time, mws.end_time, mws.manager_id
    ),
    monthly_avg AS (
SELECT month_start, AVG(shift_revenue)::numeric(14,2) AS avg_revenue_per_shift
FROM shift_revenue
GROUP BY month_start
    )
SELECT
    to_char(sr.month_start, 'YYYY-MM') AS period,
    sr.shift_id,
    sr.shift_date,
    sr.start_time AS shift_start_time,
    sr.end_time   AS shift_end_time,
    u.email       AS manager_email,
    sr.shift_revenue,
    ma.avg_revenue_per_shift,
    (sr.shift_revenue - ma.avg_revenue_per_shift)::numeric(14,2) AS above_by
FROM shift_revenue sr
         JOIN monthly_avg ma ON ma.month_start = sr.month_start
         JOIN managers m     ON m.employee_id = sr.manager_id
         JOIN employees e    ON e.user_id     = m.employee_id
         JOIN users u        ON u.id          = e.user_id
WHERE sr.shift_revenue > ma.avg_revenue_per_shift
ORDER BY period DESC, sr.shift_revenue DESC, sr.shift_date DESC;

Monthly revenue vs labor cost

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.datetime) as operation_month,
        SUM(oi.quantity * oi.price) as revenue
    FROM orders o
             JOIN order_items oi ON o.id = oi.order_id
    GROUP BY DATE_TRUNC('month', o.datetime)
),
     monthly_labor_cost AS (
         SELECT
             monthly_assignments.operation_month,
             SUM(e.gross_salary) as labor_cost
         FROM (
                  SELECT DISTINCT
                      DATE_TRUNC('month', s.date) as operation_month,
                      a.employee_id
                  FROM shifts s
                           JOIN assignments a ON s.id = a.shift_id
              ) as monthly_assignments
                  JOIN employees e ON monthly_assignments.employee_id = e.user_id
         GROUP BY monthly_assignments.operation_month
     )
SELECT
    TO_CHAR(COALESCE(mr.operation_month, mlc.operation_month), 'YYYY-MM') as period,
    ROUND(COALESCE(mr.revenue, 0)::numeric, 2) as total_revenue,
    ROUND(COALESCE(mlc.labor_cost, 0)::numeric, 2) as total_labor_cost,
    ROUND(
            CASE
                WHEN COALESCE(mr.revenue, 0) > 0
                    THEN (COALESCE(mlc.labor_cost, 0) / mr.revenue * 100)
                ELSE 0
                END::numeric, 2
        ) as labor_as_percent_of_revenue
FROM monthly_revenue mr
         FULL OUTER JOIN monthly_labor_cost mlc ON mr.operation_month = mlc.operation_month
ORDER BY period DESC;

Server performance & revenue ranking

WITH server_metrics AS (
    SELECT
        fs.employee_id,
        u.email as server_email,
        COUNT(DISTINCT a.id) as total_assignments,
        COUNT(DISTINCT o.id) as orders_processed,
        COALESCE(SUM(oi.quantity * oi.price), 0) as total_revenue_generated
    FROM front_staff fs
             JOIN employees e ON fs.employee_id = e.user_id
             JOIN users u ON e.user_id = u.id
             JOIN staff_roles sr ON fs.staff_role_id = sr.id
             LEFT JOIN assignments a ON fs.employee_id = a.employee_id
             LEFT JOIN shifts s ON a.shift_id = s.id
             LEFT JOIN tab_orders to2 ON to2.front_staff_id = fs.employee_id
             LEFT JOIN orders o ON o.id = to2.order_id
        AND o.datetime >= CURRENT_DATE - INTERVAL '3 months'
    LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE LOWER(sr.name) = 'server'
GROUP BY fs.employee_id, u.email, u.phone_number,
    e.net_salary, e.gross_salary, fs.tip_percent, sr.name
    ),
    performance_ranking AS (
SELECT *,
    RANK() OVER (ORDER BY total_revenue_generated DESC) as revenue_rank,
    RANK() OVER (ORDER BY orders_processed DESC) as orders_rank,
    CASE
    WHEN total_assignments > 0
    THEN (orders_processed::float / total_assignments)
    ELSE 0
    END as orders_per_assignment,
    CASE
    WHEN orders_processed > 0
    THEN total_revenue_generated / orders_processed
    ELSE 0
    END as avg_revenue_per_order
FROM server_metrics
    )
SELECT
    server_email,
    total_assignments,
    orders_processed,
    total_revenue_generated,
    revenue_rank,
    orders_rank,
    ROUND(orders_per_assignment::numeric, 2) as avg_orders_per_shift,
    ROUND(avg_revenue_per_order::numeric, 2) as avg_order_value
FROM performance_ranking
ORDER BY total_revenue_generated DESC, orders_processed DESC;

Reservations by Day of Week

CREATE OR REPLACE FUNCTION get_reservations_by_day(p_start_date DATE, p_end_date DATE)
RETURNS TABLE(day_of_week INT, total_reservations BIGINT) AS $$
BEGIN
    RETURN QUERY
    SELECT
        EXTRACT(DOW FROM datetime)::INT AS day_of_week,  -- 0=Sunday, 1=Monday, etc.
        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;
END;
$$ LANGUAGE plpgsql;

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;

Yearly Revenue Report

CREATE OR REPLACE FUNCTION get_yearly_revenue(p_start_date DATE, p_end_date DATE)
RETURNS TABLE(payment_year DATE, total_revenue NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT
        date_trunc('year', created_at)::DATE AS payment_year,*
        SUM(amount) AS total_revenue
    FROM payments
    WHERE DATE(created_at) BETWEEN p_start_date AND p_end_date
    GROUP BY date_trunc('year', created_at)
    ORDER BY payment_year;
END;
$$ LANGUAGE plpgsql;