Просечно време помеѓу нарачка и плаќање

Опис

Извештајот го пресметува просечното време (во минути) помеѓу креирањето на нарачката и нејзиното плаќање, групирано по келнер. Овозможува идентификување на најефикасните келнери.

SQL решение

CREATE OR REPLACE FUNCTION get_avg_time_to_payment()
RETURNS TABLE (
    user_id INT,
    waiter_name TEXT,
    total_orders BIGINT,
    avg_minutes_to_payment NUMERIC,
    min_minutes NUMERIC,
    max_minutes NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.user_id,
        (u.first_name || ' ' || u.last_name)::TEXT AS waiter_name,
        COUNT(o.order_id) AS total_orders,
        ROUND(AVG(EXTRACT(EPOCH FROM (p.payment_date - o.created_at)) / 60), 2) AS avg_minutes_to_payment,
        MIN(EXTRACT(EPOCH FROM (p.payment_date - o.created_at)) / 60) AS min_minutes,
        MAX(EXTRACT(EPOCH FROM (p.payment_date - o.created_at)) / 60) AS max_minutes
    FROM project.orders o
    JOIN project.payment p ON p.order_id = o.order_id
    JOIN project.app_user u ON u.user_id = o.user_id
    WHERE o.status = 'ПЛАТЕНА'
    GROUP BY u.user_id, u.first_name, u.last_name
    HAVING COUNT(o.order_id) >= 5
    ORDER BY avg_minutes_to_payment ASC;
END;
$$;

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

U(user_id, first_name, last_name)
O(order_id, user_id, created_at, status)
P(payment_id, order_id, payment_date)

J1 ← O ⨝ O.order_id = P.order_id P
J2 ← J1 ⨝ O.user_id = U.user_id U

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

G ← γ user_id, waiter_name;
     COUNT(order_id) → total_orders,
     AVG((payment_date - created_at)/60) → avg_minutes_to_payment,
     MIN((payment_date - created_at)/60) → min_minutes,
     MAX((payment_date - created_at)/60) → max_minutes (F1)

F2 ← σ total_orders ≥ 5 (G)

R ← π user_id, waiter_name, total_orders, avg_minutes_to_payment, min_minutes, max_minutes (F2)

R_final ← τ avg_minutes_to_payment ASC (R)