Келнери со надпросечна ефикасност и нивниот најпрофитабилен производ

Опис

Извештајот ги прикажува келнерите чие просечно време до плаќање е помало од просекот на сите келнери, заедно со производот што им донел најголем приход. Користи повеќе нивоа на вгнездување: (1) агрегација по келнер, (2) споредба со глобален просек, (3) корелационен потпрашалник за најпрофитабилен производ.

SQL решение

CREATE OR REPLACE FUNCTION project.get_efficient_waiters_top_product()
RETURNS TABLE (
    user_id INT,
    waiter_name TEXT,
    avg_minutes NUMERIC,
    global_avg_minutes NUMERIC,
    total_orders BIGINT,
    top_product TEXT,
    top_product_revenue NUMERIC
)
LANGUAGE sql
AS $$
    WITH waiter_stats AS (
        SELECT 
            u.user_id AS uid,
            (u.first_name || ' ' || u.last_name)::TEXT AS wname,
            AVG(EXTRACT(EPOCH FROM (p.payment_date - o.created_at)) / 60)::numeric AS avg_min,
            COUNT(DISTINCT o.order_id) AS total_orders
        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
    ),
    global_avg AS (
        SELECT AVG(avg_min)::numeric AS g_avg FROM waiter_stats
    )
    SELECT 
        ws.uid,
        ws.wname,
        ROUND(ws.avg_min, 2),
        ROUND(ga.g_avg, 2),
        ws.total_orders,
        (
            SELECT p.name::TEXT
            FROM project.order_item oi2
            JOIN project.orders o2 ON o2.order_id = oi2.order_id
            JOIN project.product p ON p.product_id = oi2.product_id
            WHERE o2.user_id = ws.uid
              AND o2.status = 'ПЛАТЕНА'
            GROUP BY p.product_id, p.name
            ORDER BY SUM(oi2.quantity * oi2.unit_price) DESC
            LIMIT 1
        ),
        (
            SELECT SUM(oi3.quantity * oi3.unit_price)::numeric
            FROM project.order_item oi3
            JOIN project.orders o3 ON o3.order_id = oi3.order_id
            JOIN project.product p3 ON p3.product_id = oi3.product_id
            WHERE o3.user_id = ws.uid
              AND o3.status = 'ПЛАТЕНА'
              AND p3.name = (
                  SELECT p4.name
                  FROM project.order_item oi4
                  JOIN project.orders o4 ON o4.order_id = oi4.order_id
                  JOIN project.product p4 ON p4.product_id = oi4.product_id
                  WHERE o4.user_id = ws.uid AND o4.status = 'ПЛАТЕНА'
                  GROUP BY p4.product_id, p4.name
                  ORDER BY SUM(oi4.quantity * oi4.unit_price) DESC
                  LIMIT 1
              )
        )
    FROM waiter_stats ws, global_avg ga
    WHERE ws.avg_min < ga.g_avg
    ORDER BY 3 ASC;
$$;

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

U(user_id, first_name, last_name)
O(order_id, user_id, created_at, status)
P(payment_id, order_id, payment_date)
OI(order_id, product_id, quantity, unit_price)
PR(product_id, name)

J1 ← O ⨝ O.order_id = P.order_id P
J2 ← J1 ⨝ O.user_id = U.user_id U
F1 ← σ status='ПЛАТЕНА' (J2)
G1 ← γ user_id, waiter_name; AVG((payment_date - created_at)/60) → avg_min,
                              COUNT(DISTINCT order_id) → total_orders (F1)

G2 ← γ ; AVG(avg_min) → g_avg (G1)

F2 ← σ avg_min < g_avg (G1 × G2)

J3 ← OI ⨝ O ⨝ PR
F3 ← σ O.user_id = F2.user_id ∧ status='ПЛАТЕНА' (J3)
G3 ← γ product_id, name; SUM(quantity * unit_price) → prod_rev (F3)
TOP ← τ prod_rev DESC (G3) [LIMIT 1]

R ← π user_id, waiter_name, avg_minutes, global_avg_minutes,
      total_orders, top_product, top_product_revenue (F2 ⨝ TOP)

R_final ← τ avg_minutes ASC (R)