Најактивни келнери според приход

Опис

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

SQL решение

CREATE OR REPLACE FUNCTION get_top_waiters()
RETURNS TABLE (
    user_id INT,
    waiter_name TEXT,
    role_name TEXT,
    total_orders BIGINT,
    total_revenue NUMERIC,
    avg_order_value NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.user_id,
        (u.first_name || ' ' || u.last_name)::TEXT AS waiter_name,
        r.role_name::TEXT AS role_name,
        COUNT(DISTINCT o.order_id) AS total_orders,
        SUM(oi.quantity * oi.unit_price) AS total_revenue,
        ROUND(AVG(oi.quantity * oi.unit_price), 2) AS avg_order_value
    FROM project.app_user u
    JOIN project.role r ON r.role_id = u.role_id
    JOIN project.orders o ON o.user_id = u.user_id
    JOIN project.order_item oi ON oi.order_id = o.order_id
    WHERE o.status = 'ПЛАТЕНА'
    GROUP BY u.user_id, u.first_name, u.last_name, r.role_name
    ORDER BY total_revenue DESC;
END;
$$;

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

U(user_id, first_name, last_name, role_id)
R(role_id, role_name)
O(order_id, user_id, status)
OI(order_id, quantity, unit_price)

J1 ← U ⨝ U.role_id = R.role_id R
J2 ← J1 ⨝ U.user_id = O.user_id O
J3 ← J2 ⨝ O.order_id = OI.order_id OI

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

G ← γ user_id, waiter_name, role_name;
     COUNT(DISTINCT order_id) → total_orders,
     SUM(quantity * unit_price) → total_revenue,
     AVG(quantity * unit_price) → avg_order_value (F1)

R ← π user_id, waiter_name, role_name, total_orders, total_revenue, avg_order_value (G)

R_final ← τ total_revenue DESC (R)