= List of products that are low on stock and high in demand
{{{#!sql
CREATE OR REPLACE FUNCTION get_low_stock_high_demand_products(
    p_stock_threshold INT,
    p_demand_threshold INT
)
RETURNS TABLE (
    product_code INT,
    product_description TEXT,
    current_stock INT,
    number_of_orders BIGINT,
    total_quantity_sold BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.code AS product_code,
        p.description AS product_description,
        p.availability AS current_stock,
        COUNT(DISTINCT o.order_num) AS number_of_orders,
        COALESCE(SUM(o.quantity), 0) AS total_quantity_sold
    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,
        p.availability
    HAVING
        p.availability < p_stock_threshold
        AND COUNT(DISTINCT o.order_num) >= p_demand_threshold
    ORDER BY
        number_of_orders DESC,
        total_quantity_sold DESC,
        current_stock ASC;
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

**Calculate demand statistics:**
- **FORMULA:** number_of_orders = COUNT(DISTINCT order_num)  

- D ← γcode, description, availability;
     COUNT(DISTINCT order_num) → number_of_orders,
     Σ(quantity) → total_quantity_sold
     (J2)

**Filter low-stock and high-demand products:**
- F ← σavailability < p_stock_threshold
     ∧ number_of_orders ≥ p_demand_threshold(D)

**Sort by highest number of orders:**
- R_final ← τnumber_of_orders DESC,
           total_quantity_sold DESC,
           availability ASC(F)