Најпрофитабилни маси

Опис

Извештајот ги прикажува масите со најголем вкупен приход, број на нарачки, просечна вредност на нарачка и приход по седиште.

SQL решение

CREATE OR REPLACE FUNCTION get_top_tables()
RETURNS TABLE (
    table_id INT,
    table_number TEXT,
    capacity INT,
    order_count BIGINT,
    total_revenue NUMERIC,
    avg_order_value NUMERIC,
    revenue_per_seat NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        rt.table_id,
        rt.table_number::TEXT,
        rt.capacity,
        COUNT(DISTINCT o.order_id) AS order_count,
        SUM(p.amount) AS total_revenue,
        ROUND(AVG(p.amount), 2) AS avg_order_value,
        ROUND(SUM(p.amount) / rt.capacity, 2) AS revenue_per_seat
    FROM project.restaurant_table rt
    JOIN project.orders o ON o.table_id = rt.table_id
    JOIN project.payment p ON p.order_id = o.order_id
    WHERE o.status = 'ПЛАТЕНА'
    GROUP BY rt.table_id, rt.table_number, rt.capacity
    ORDER BY total_revenue DESC;
END;
$$;

Релациона алгебра

RT(table_id, table_number, capacity)
O(order_id, table_id, status)
P(payment_id, order_id, amount)

J1 ← RT ⨝ RT.table_id = O.table_id O
J2 ← J1 ⨝ O.order_id = P.order_id P

F1 ← σ status='ПЛАТЕНА' (J2)

G ← γ table_id, table_number, capacity;
     COUNT(DISTINCT order_id) → order_count,
     SUM(amount) → total_revenue,
     AVG(amount) → avg_order_value,
     SUM(amount)/capacity → revenue_per_seat (F1)

R ← π table_id, table_number, capacity, order_count, total_revenue, avg_order_value, revenue_per_seat (G)

R_final ← τ total_revenue DESC (R)