Changes between Initial Version and Version 1 of AdvancedReport4

Timestamp:

08/21/26 05:40:04 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport4

@@ +1 @@
+= Each product's monthly sales
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_products_monthly_sales()
+RETURNS TABLE (
+    product_code INT,
+    product_description TEXT,
+    year INT,
+    month INT,
+    number_of_orders BIGINT,
+    total_quantity_sold BIGINT,
+    total_revenue NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        p.code AS product_code,
+        p.description AS product_description,
+        EXTRACT(YEAR FROM o.last_modified_date)::INT AS year,
+        EXTRACT(MONTH FROM o.last_modified_date)::INT AS month,
+        COUNT(DISTINCT o.order_num) AS number_of_orders,
+        SUM(o.quantity) AS total_quantity_sold,
+        SUM(
+            p.price
+            * o.quantity
+            * (1 - COALESCE(o.discount, 0) / 100.0)
+        ) AS total_revenue
+    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,
+        EXTRACT(YEAR FROM o.last_modified_date),
+        EXTRACT(MONTH FROM o.last_modified_date)
+    ORDER BY
+        year DESC,
+        month DESC,
+        total_revenue DESC;
+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
+
+**Extract year and month:**
+- D ← πcode, description, price, order_num, quantity,
+     discount,
+     YEAR(last_modified_date) → year,
+     MONTH(last_modified_date) → month
+     (J2)
+
+**Calculate revenue for each induvidual order:**
+- **FORMULA:** order_revenue = price × quantity × (1 - COALESCE(discount, 0) / 100)
+- R1 ← πcode, description, year, month,
+      order_num, quantity,
+      price × quantity ×
+      (1 - COALESCE(discount, 0) / 100)
+      → order_revenue(D)
+
+**Agregate monthly sales:**
+- R2 ← γcode, description, year, month;
+     COUNT(DISTINCT order_num) → number_of_orders,
+     Σ(quantity) → total_quantity_sold,
+     Σ(order_revenue) → total_revenue
+     (R1)
+
+
+**Sort by total revenue:**
+- R_final ← τyear DESC, month DESC, total_revenue DESC(R2)
+
+