Changes between Initial Version and Version 1 of AdvancedReport3

Timestamp:

08/21/26 05:33:40 (30 hours ago)

Author:

235018

Comment:

--

AdvancedReport3

@@ +1 @@
+= List of products that are low on stock and high in demand
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_low_stock_high_demand_products(
+    p_stock_threshold INT,
+    p_demand_threshold INT
+)
+RETURNS TABLE (
+    product_code INT,
+    product_description TEXT,
+    current_stock INT,
+    number_of_orders BIGINT,
+    total_quantity_sold BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        p.code AS product_code,
+        p.description AS product_description,
+        p.availability AS current_stock,
+        COUNT(DISTINCT o.order_num) AS number_of_orders,
+        COALESCE(SUM(o.quantity), 0) AS total_quantity_sold
+    FROM product p
+    JOIN includes i
+        ON p.code = i.code
+    JOIN "order" o
+        ON i.order_num = o.order_num
+    GROUP BY
+        p.code,
+        p.description,
+        p.availability
+    HAVING
+        p.availability < p_stock_threshold
+        AND COUNT(DISTINCT o.order_num) >= p_demand_threshold
+    ORDER BY
+        number_of_orders DESC,
+        total_quantity_sold DESC,
+        current_stock ASC;
+END;
+$$;
+
+}}}
+
+== Relational Algebra
+- P(code, price, availability, description, ...)
+- O(order_num, quantity, status, last_modified_date, payment_method, discount)
+- I(code, order_num)
+
+**JOIN products with orders:**
+- J1 ← P ⟕P.code = I.code I
+- J2 ← J1 ⨝I.order_num = O.order_num O
+
+**Calculate demand statistics:**
+- **FORMULA:** number_of_orders = COUNT(DISTINCT order_num)  
+
+- D ← γcode, description, availability;
+     COUNT(DISTINCT order_num) → number_of_orders,
+     Σ(quantity) → total_quantity_sold
+     (J2)
+
+**Filter low-stock and high-demand products:**
+- F ← σavailability < p_stock_threshold
+     ∧ number_of_orders ≥ p_demand_threshold(D)
+
+**Sort by highest number of orders:**
+- R_final ← τnumber_of_orders DESC,
+           total_quantity_sold DESC,
+           availability ASC(F)
+
+