Changes between Initial Version and Version 1 of AdvancedReport18

Timestamp:

08/21/26 07:00:23 (28 hours ago)

Author:

235018

Comment:

--

AdvancedReport18

@@ +1 @@
+= Stores ordered by monthly profit including monthly revenue growth
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_stores_by_monthly_profit_and_revenue_growth()
+RETURNS TABLE (
+    store_id INT,
+    store_name TEXT,
+    month_and_year TEXT,
+    monthly_profit NUMERIC,
+    previous_month_revenue NUMERIC,
+    current_month_revenue NUMERIC,
+    revenue_growth NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    WITH monthly_revenue AS (
+        SELECT
+            s.store_id,
+            s.name AS store_name,
+            DATE_TRUNC('month', o.last_modified_date) AS month_date,
+            SUM(
+                p.price
+                * o.quantity
+                * (1 - COALESCE(o.discount, 0) / 100.0)
+            ) AS 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
+        GROUP BY
+            s.store_id,
+            s.name,
+            DATE_TRUNC('month', o.last_modified_date)
+    ),
+    revenue_with_previous AS (
+        SELECT
+            store_id,
+            store_name,
+            month_date,
+            revenue,
+            LAG(revenue) OVER (
+                PARTITION BY store_id
+                ORDER BY month_date
+            ) AS previous_month_revenue
+        FROM monthly_revenue
+    )
+    SELECT
+        store_id,
+        store_name,
+        TO_CHAR(month_date, 'YYYY-MM') AS month_and_year,
+        revenue AS monthly_profit,
+        COALESCE(previous_month_revenue, 0) AS previous_month_revenue,
+        revenue AS current_month_revenue,
+        revenue - COALESCE(previous_month_revenue, 0) AS revenue_growth
+    FROM revenue_with_previous
+    ORDER BY
+        monthly_profit DESC,
+        revenue_growth 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 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
+
+**Calculate revenue for each order and group it by store and month:**
+- **FORMULA:** order_revenue = price × quantity × (1 - COALESCE(discount, 0) / 100)
+- R1 ← γstore_id, name, YEAR(last_modified_date),
+     MONTH(last_modified_date);
+     Σ(order_revenue) → monthly_revenue
+     (J4)
+
+**Calculate previous month revenue for each store:**
+- R2 ← γstore_id, name, month;
+     LAG(monthly_revenue)
+     OVER (PARTITION BY store_id ORDER BY month)
+     → previous_month_revenue
+     (R1)
+
+**Calculate monthly revenue growth:**
+- R3 ← πstore_id, name, month,
+     monthly_revenue,
+     previous_month_revenue,
+     monthly_revenue - previous_month_revenue
+     → revenue_growth
+     (R2)
+
+**Sort by total monthly revenue and growth:**
+- R_final ← τmonthly_revenue DESC,
+           revenue_growth DESC(R3)
+
+