Products ordered by number of orders from highest to lowest

CREATE OR REPLACE FUNCTION get_products_by_number_of_orders()
RETURNS TABLE (
    product_code INT,
    product_description TEXT,
    product_price NUMERIC,
    number_of_orders BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.code AS product_code,
        p.description AS product_description,
        p.price AS product_price,
        COUNT(DISTINCT o.order_num) AS number_of_orders
    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.price
    ORDER BY
        number_of_orders 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

Calculate the number of distinct orders the product has been in:

• R ← γcode, description, price;

COUNT(DISTINCT order_num) → number_of_orders (J2)

Sort by total orders:

• R_final ← τnumber_of_orders DESC(R)