= Clients ordered by number of orders
{{{#!sql
CREATE OR REPLACE FUNCTION get_clients_by_number_of_orders()
RETURNS TABLE (
    client_id INT,
    client_name TEXT,
    number_of_orders BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        c.client_id,
        c.name AS client_name,
        COUNT(DISTINCT o.order_num) AS number_of_orders
    FROM client c
    JOIN makes_order mo
        ON c.client_id = mo.client_id
    JOIN "order" o
        ON mo.order_num = o.order_num
    GROUP BY
        c.client_id,
        c.name
    ORDER BY
        number_of_orders DESC;
END;
$$;

}}}

== Relational Algebra
- O(order_num, quantity, status, last_modified_date, payment_method, discount)
- C(client_id, name, first_name, last_name, email, password, delivery_address)
- MO(client_id, order_num)


**JOIN clients with their orders:**
- J1 ← C ⨝C.client_id = MO.client_id MO
- J2 ← J1 ⨝MO.order_num = O.order_num O

**Calculate number of distinct orders for each lient:**
- R ← γclient_id, name;
     COUNT(DISTINCT order_num) → number_of_orders
     (J2)

**Sort by total numebr of orders:**
- R_final ← τnumber_of_orders DESC(R)