Changes between Initial Version and Version 1 of AdvancedReport6

Timestamp:

08/21/26 05:53:12 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport6

@@ +1 @@
+= List of products who have not been ordered
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_products_by_total_sales()
+RETURNS TABLE (
+    product_code INT,
+    product_description TEXT,
+    product_price NUMERIC,
+    number_of_orders BIGINT,
+    total_revenue NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        p.code AS product_code,
+        p.description AS product_description,
+        p.price AS product_price,
+        COUNT(DISTINCT o.order_num) AS number_of_orders,
+        COALESCE(
+            SUM(
+                p.price
+                * o.quantity
+                * (1 - COALESCE(o.discount, 0) / 100.0)
+            ),
+            0
+        ) AS total_revenue
+    FROM product p
+    LEFT JOIN includes i
+        ON p.code = i.code
+    LEFT JOIN "order" o
+        ON i.order_num = o.order_num
+    GROUP BY
+        p.code,
+        p.description,
+        p.price
+    ORDER BY
+        number_of_orders DESC,
+        total_revenue DESC;
+END;
+$$;
+CREATE OR REPLACE FUNCTION get_products_never_ordered()
+RETURNS TABLE (
+    product_code INT,
+    product_description TEXT,
+    product_price NUMERIC,
+    current_stock INT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        p.code AS product_code,
+        p.description AS product_description,
+        p.price AS product_price,
+        p.availability AS current_stock
+    FROM product p
+    LEFT JOIN includes i
+        ON p.code = i.code
+    WHERE i.order_num IS NULL
+    ORDER BY p.code;
+END;
+$$;
+}}}
+
+== Relational Algebra
+- P(code, price, availability, description, ...)
+- I(code, order_num)
+
+**JOIN products with orders:**
+- J1 ← P ⟕P.code = I.code I
+- J2 ← J1 ⟕I.order_num = O.order_num O
+
+**FILTER products that haven't been ordered yet:**
+- R ← σorder_num IS NULL(J1)
+
+**Sort by product code:**
+- R_final ← τcode ASC(R)
+
+