DatabaseProgramming: 05 Customer subscription summary.sql

CREATE OR REPLACE FUNCTION public.fn_get_customer_subscription_summary(p_customer_id bigint)
RETURNS text
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_active_count integer;
    v_suspended_count integer;
    v_closed_count integer;
    v_monthly_fee numeric;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM public.customers WHERE customer_id = p_customer_id) THEN
        RETURN format('Customer %s does not exist.', p_customer_id);
    END IF;

    SELECT COUNT(*) FILTER (WHERE s.status = 'active'),
           COUNT(*) FILTER (WHERE s.status = 'suspended'),
           COUNT(*) FILTER (WHERE s.status IN ('cancelled', 'expired'))
    INTO v_active_count, v_suspended_count, v_closed_count
    FROM public.accounts a
    LEFT JOIN public.subscriptions s ON s.account_id = a.account_id
    WHERE a.customer_id = p_customer_id;

    v_monthly_fee := public.fn_get_customer_monthly_recurring_charge(p_customer_id);

    RETURN format(
        'Active subscriptions: %s, Suspended subscriptions: %s, Ended subscriptions: %s, Monthly recurring charge: %s MKD',
        v_active_count, v_suspended_count, v_closed_count, v_monthly_fee
    );
END;
$$;