Changes between Initial Version and Version 1 of AdvancedReport9

Timestamp:

08/21/26 06:09:18 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport9

@@ +1 @@
+= Store with highest revenue growth in the last calendar year
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_store_with_highest_revenue_growth()
+RETURNS TABLE (
+    store_id INT,
+    store_name TEXT,
+    previous_year_revenue NUMERIC,
+    last_year_revenue NUMERIC,
+    revenue_growth NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    WITH yearly_revenue AS (
+        SELECT
+            s.store_id,
+            s.name AS store_name,
+            EXTRACT(YEAR FROM o.last_modified_date)::INT AS year,
+            SUM(
+                p.price
+                * o.quantity
+                * (1 - COALESCE(o.discount, 0) / 100.0)
+            ) AS total_revenue
+        FROM store s
+        JOIN sells se
+            ON s.store_id = se.store_id
+        JOIN product p
+            ON se.code = p.code
+        JOIN includes i
+            ON p.code = i.code
+        JOIN "order" o
+            ON i.order_num = o.order_num
+        WHERE o.last_modified_date >=
+              date_trunc('year', CURRENT_DATE) - INTERVAL '2 years'
+          AND o.last_modified_date <
+              date_trunc('year', CURRENT_DATE)
+        GROUP BY
+            s.store_id,
+            s.name,
+            EXTRACT(YEAR FROM o.last_modified_date)
+    ),
+    revenue_comparison AS (
+        SELECT
+            store_id,
+            store_name,
+            MAX(
+                CASE
+                    WHEN year = EXTRACT(YEAR FROM CURRENT_DATE)::INT - 2
+                    THEN total_revenue
+                    ELSE 0
+                END
+            ) AS previous_year_revenue,
+            MAX(
+                CASE
+                    WHEN year = EXTRACT(YEAR FROM CURRENT_DATE)::INT - 1
+                    THEN total_revenue
+                    ELSE 0
+                END
+            ) AS last_year_revenue
+        FROM yearly_revenue
+        GROUP BY
+            store_id,
+            store_name
+    )
+    SELECT
+        store_id,
+        store_name,
+        previous_year_revenue,
+        last_year_revenue,
+        last_year_revenue - previous_year_revenue AS revenue_growth
+    FROM revenue_comparison
+    ORDER BY revenue_growth DESC
+    LIMIT 1;
+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 their products:**
+- 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
+
+**SELECT orders from the last TWO calendar years:**
+- F1 ← σ last_modified_date ≥ START_OF_CURRENT_YEAR - 2 YEARS
+∧
+last_modified_date < START_OF_CURRENT_YEAR
+(J4)
+
+**Calculate revenue for each induvidual order:**
+- R1 ← πstore_id, name, YEAR(last_modified_date) → year,
+      price × quantity ×
+      (1 - COALESCE(discount, 0) / 100)
+      → order_revenue(F1)
+
+**Calculate total revenue for each store and year:**
+- R3 ← γstore_id, name;
+     MAX(CASE WHEN year = current_year - 2
+         THEN total_revenue ELSE 0 END)
+         → previous_year_revenue,
+
+     MAX(CASE WHEN year = current_year - 1
+         THEN total_revenue ELSE 0 END)
+         → last_year_revenue
+     (R2)
+
+**Calculate revenue growth:**
+- R4 ← πstore_id, name,
+      previous_year_revenue,
+      last_year_revenue,
+      last_year_revenue - previous_year_revenue
+      → revenue_growth(R3)
+
+**Sort by revenue growth:**
+- R5 ← τrevenue_growth DESC(R4)
+
+**SELECT store with highest revenue growth:**
+- R_final ← γLIMIT 1(R5)