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 a.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 shifts s ON DATE(s.date) = dates.operation_date
    LEFT JOIN assignments a ON s.id = a.shift_id
GROUP BY dates.operation_date
ORDER BY dates.operation_date DESC;

Relational algebra:

We will use:

The generated series as given in our relational algebra and noted as D, Reservations as R, Orders as O, OrderItems as OI, Shifts as S and Assignments as A

operation_dateℑdates_.operation_date, total_reservations=COUNT(R.id),total_orders=COUNT(O.id),unique_costumers=COUNT(R.user_id), active_employees=COUNT(A.employee_id), daily_revenue=SUM(OI.quantity*OI.price) 

(((((R ⟕ r.datetime = dates.operation_date D) ⟕ dates.operation_date=o.datetime O) ⟕ o.id=oi.order_id OI) ⟕ dates.operation_date=s.date S) ⟕ s.id=a.shift_id A)

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;

Relational algebra:

We will use:

OnlineOrders as OO, Payments as P, Orders as O and TabORders as TORD

π order_type=’OnlineOrders’,total_revenue=SUM(amount)(σ p_start_date<=o.o_timestamp<=p_end_date(OO⋈OO.order_id=O.id(P⋈P.order_id=O.id O))

U

π order_type=’TabOrders’,total_revenue=SUM(amount)(σ p_start_date<=o.o_timestamp<=p_end_date(TORD⋈TORD.order_id=O.order_id (P ⋈P.order_id=O.id O))

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;

Relational algebra:

We will use:

Products as P, OrderItems as OI, Category as C and TabOrders as TORD

p.id,p.name,c.nameℑproduct_id=P.id,product_name=P.name,category_name=C.name,total_quantity=SUM(oi.quantity), revenue_share_percent=100.0 * SUM(OI.quantity * OI.price) / SUM(SUM(OI.quantity * OI.price))
  
(σ O.datetime>= current_date-90(((C ⋈P.category_id = c_id P) ⋈P.id=OI.product_id OI) ⋈o.id=oi.order_id O))

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;

Relational algebra:

We refer Shift as S, OrderItems as OI, Orders as O

DSP = πstart_t=start_time,end_t=end_time(S)

period, shift_periodℱtotal_revenue=SUM(OI.price*OI.quantity) 

((O ⋈ O.id = OI.order_id OI) ⋈ O.datetime::time>DSP.start_t ⋀ O.datetime::time<DSP.end_t DSP))

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;

Relational algebra:

We use manager_worked_shifts as MWS, shift_revenue as SR mothly_avg as MA, Shifts as S, Assignment as A, Managers as M, Employees as E, Users as U and end_date= (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')

MWS = π shift_id=S.id, S.date, S.start_time,S.end_time,manager_id=A.employee_id
(σS.date>=date⋀ S.date<end_date)((A ⋈S.id=A.shift_id S) ⋈ A.employee_id= M.employee_id M))

SR =mws.shift_id, month_start, mws.date, mws.start_time, mws.end_time, mws.manager_id ℱshift_revenue = SUM(oi.quantity * oi.price)
(( MWS ⟕ o.datetime::date=mws.date ⋀ o.datetime::time >= mws.start_time ⋀ O.datetime::time < MWS.end_time O) ⟕ O.id=OI.id OI) 

MA = month_start ℱ avg_revenue_per_shift= AVG(SR.shift_revenue)(SR)

π period=sr.month_start,sr.shift_id,sr.shift_date, shift_start_time=sr.start_time, shift_end_time=sr.send_time, manager_email = u.email,sr.shift_revenue, ma.avg_revenue_per_shift, above_by = sr.shift_revenue - ma.avg_revenue_per_shift
(σ sr.shift_revenue > ma.avg_revenue_per_shift((((SR ⋈SR.month_start=MA.month_start MA) ⋈ SR.manager_id=M.employee_id M) ⋈  m.employee_id=e.user_id E) ⋈ e.user_id=u.id U))

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 orders o ON o.employee_id = fs.employee_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;