Approximate number of orders per client

CREATE OR REPLACE FUNCTION get_approximate_orders_per_client()
RETURNS TABLE (
    total_clients BIGINT,
    total_orders BIGINT,
    approximate_orders_per_client NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        (SELECT COUNT(*) FROM client) AS total_clients,
        (SELECT COUNT(*) FROM "order") AS total_orders,
        ROUND(
            (SELECT COUNT(*) FROM "order")::NUMERIC
            / NULLIF((SELECT COUNT(*) FROM client), 0),
            2
        ) AS approximate_orders_per_client;
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)

Calculate total number of orders:

• O_total ← γCOUNT(order_num) → total_orders(O)

Calculate total number of clients:

• C_total ← γCOUNT(client_id) → total_clients(C)

Calculate approximate number of orders per client:

• R ← γclient_id, name; R ← πtotal_clients,

total_orders, total_orders / total_clients → approximate_orders_per_client (C_total × O_total)