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