DatabaseProgramming: 02 Outstanding customer balance.sql

CREATE OR REPLACE FUNCTION public.fn_get_customer_outstanding_balance(p_customer_id bigint)
RETURNS numeric
LANGUAGE sql
STABLE
AS $$
    SELECT GREATEST(COALESCE(SUM(invoice_balance), 0), 0)
    FROM (
        SELECT i.invoice_id,
               i.total_amount - COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'completed'), 0) AS invoice_balance
        FROM public.accounts a
        JOIN public.invoices i ON i.account_id = a.account_id
        LEFT JOIN public.payments p ON p.invoice_id = i.invoice_id
        WHERE a.customer_id = p_customer_id
          AND i.status <> 'cancelled'
        GROUP BY i.invoice_id, i.total_amount
    ) balances;
$$;