Changes between Initial Version and Version 1 of AdvancedReport5

Timestamp:

08/21/26 05:48:22 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport5

@@ +1 @@
+= Stores ordered by total revenue in the last calendar year from highest to lowest
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_stores_by_last_calendar_year_revenue()
+RETURNS TABLE (
+    store_id INT,
+    store_name TEXT,
+    number_of_orders BIGINT,
+    total_quantity_sold BIGINT,
+    total_revenue NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        s.store_id,
+        s.name AS store_name,
+        COUNT(DISTINCT o.order_num) AS number_of_orders,
+        COALESCE(SUM(o.quantity), 0) AS total_quantity_sold,
+        COALESCE(
+            SUM(
+                p.price
+                * o.quantity
+                * (1 - COALESCE(o.discount, 0) / 100.0)
+            ),
+            0
+        ) AS total_revenue
+    FROM store s
+    LEFT JOIN sells se
+        ON s.store_id = se.store_id
+    LEFT JOIN product p
+        ON se.code = p.code
+    LEFT JOIN includes i
+        ON p.code = i.code
+    LEFT JOIN "order" o
+        ON i.order_num = o.order_num
+        AND o.last_modified_date >= DATE_TRUNC(
+            'year',
+            CURRENT_DATE
+        ) - INTERVAL '1 year'
+        AND o.last_modified_date < DATE_TRUNC(
+            'year',
+            CURRENT_DATE
+        )
+    GROUP BY
+        s.store_id,
+        s.name
+    ORDER BY
+        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)
+- S(store_ID, name, date_of_founding, physical_address, store_email, rating)
+- SE(code, store_ID, quantity, discount)
+
+
+**JOIN stores with products they sell:**
+- J1 ← S ⟕S.store_ID = SE.store_ID SE
+- J2 ← J1 ⟕SE.code = P.code P
+
+**JOIN products with orders:**
+- J3 ← J2 ⟕P.code = I.code I
+- J4 ← J3 ⟕I.order_num = O.order_num O
+
+**FILTER orders from the last calendar year:**
+- F ← σlast_modified_date ≥ start_date
+     ∧ last_modified_date < end_date(J4)
+
+**Calculate revenue for each order:**
+- **FORMULA:** order_revenue = order_total × (1 - COALESCE(discount, 0) / 100)
+
+- R1 ← πstore_ID, store_name, order_num, quantity,
+      price × quantity ×
+      (1 - COALESCE(discount, 0) / 100)
+      → order_revenue(F)
+
+**Calculate revenue for each store:**
+- R2 ← γstore_ID, store_name;
+     COUNT(DISTINCT order_num) → number_of_orders,
+     Σ(quantity) → total_quantity_sold,
+     Σ(order_revenue) → total_revenue
+     (R1)
+- For stores without orders in the last calendar year:
+    - number_of_orders = 0
+    - total_quantity_sold = 0
+    - total_revenue = 0
+
+**Sort by store revenue:**
+- R_final ← τtotal_revenue DESC(R2)
+
+