Changes between Initial Version and Version 1 of AdvancedReport5

Timestamp:

09/24/26 02:08:13 (7 days ago)

Author:

201178

Comment:

--

AdvancedReport5

@@ +1 @@
+= Најактивни келнери според приход =
+
+== Опис ==
+
+Извештајот ги прикажува сите келнери со вкупниот број на нарачки, вкупниот приход и просечната вредност на нарачка.
+
+== 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)
+}}}