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