= 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)