DatabaseProgramming: Customer subscription summary.sql

CREATE OR REPLACE FUNCTION fn_get_customer_subscription_summary(
    p_customer_id BIGINT
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_active_count INT;
    v_suspended_count INT;
    v_monthly_fee NUMERIC;
BEGIN
    SELECT
        COUNT(*) FILTER (WHERE s.status = 'active'),
        COUNT(*) FILTER (WHERE s.status = 'suspended'),
        COALESCE(SUM(p.monthly_fee) FILTER (WHERE s.status = 'active'), 0)
    INTO
        v_active_count,
        v_suspended_count,
        v_monthly_fee
    FROM accounts a
    JOIN subscriptions s
        ON s.account_id = a.account_id
    JOIN plans p
        ON p.plan_id = s.plan_id
    WHERE a.customer_id = p_customer_id;

    RETURN
        'Active subscriptions: ' || v_active_count ||
        ', Suspended subscriptions: ' || v_suspended_count ||
        ', Active monthly fee: ' || v_monthly_fee;
END;
$$;

SELECT fn_get_customer_subscription_summary(1);