= Each product's monthly sales
{{{#!sql
CREATE OR REPLACE FUNCTION get_products_monthly_sales()
RETURNS TABLE (
    product_code INT,
    product_description TEXT,
    year INT,
    month INT,
    number_of_orders BIGINT,
    total_quantity_sold BIGINT,
    total_revenue NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.code AS product_code,
        p.description AS product_description,
        EXTRACT(YEAR FROM o.last_modified_date)::INT AS year,
        EXTRACT(MONTH FROM o.last_modified_date)::INT AS month,
        COUNT(DISTINCT o.order_num) AS number_of_orders,
        SUM(o.quantity) AS total_quantity_sold,
        SUM(
            p.price
            * o.quantity
            * (1 - COALESCE(o.discount, 0) / 100.0)
        ) AS total_revenue
    FROM product p
    JOIN includes i
        ON p.code = i.code
    JOIN "order" o
        ON i.order_num = o.order_num
    GROUP BY
        p.code,
        p.description,
        EXTRACT(YEAR FROM o.last_modified_date),
        EXTRACT(MONTH FROM o.last_modified_date)
    ORDER BY
        year DESC,
        month DESC,
        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)

**JOIN products with orders:**
- J1 ← P ⟕P.code = I.code I
- J2 ← J1 ⟕I.order_num = O.order_num O

**Extract year and month:**
- D ← πcode, description, price, order_num, quantity,
     discount,
     YEAR(last_modified_date) → year,
     MONTH(last_modified_date) → month
     (J2)

**Calculate revenue for each induvidual order:**
- **FORMULA:** order_revenue = price × quantity × (1 - COALESCE(discount, 0) / 100)
- R1 ← πcode, description, year, month,
      order_num, quantity,
      price × quantity ×
      (1 - COALESCE(discount, 0) / 100)
      → order_revenue(D)

**Agregate monthly sales:**
- R2 ← γcode, description, year, month;
     COUNT(DISTINCT order_num) → number_of_orders,
     Σ(quantity) → total_quantity_sold,
     Σ(order_revenue) → total_revenue
     (R1)


**Sort by total revenue:**
- R_final ← τyear DESC, month DESC, total_revenue DESC(R2)