Getting information on users payments via subscriptions
I find that if I try to write out what my query is doing, it tends to illuminate the answer

-- for everything that is in payment 
-- that doesn't have a refund date and doesn't have a deleted at
-- And the payments have a subscription and a user
-- give me their subscription id and the count of subscriptions
SELECT 
    COUNT(subscription.user_id) as cnt
,   subscription.id 
FROM 
    payment
    INNER JOIN 
        subscription 
        ON payment.subscription_id = subscription.id
    INNER JOIN 
        user 
        ON user.id = subscription.user_id
WHERE 
    (payment.refunded_date is null) 
    AND (subscription.deleted_at IS NULL)
GROUP BY 
    subscription.id
ORDER BY 
    cnt


Assuming I read your query correctly, I think your issue is that you are counting how many subscriptions an ID has. You didn't specify how the results were wrong, but I'm guessing you get 1 back.

If you want to know the total count of payments, you would count that instead.

-- for everything that is in payment 
-- that doesn't have a refund date and doesn't have a deleted at
-- And the payments have a subscription and a user
-- give me their subscription id and the count of payments
SELECT 
    COUNT(payment.subscription_id) as cnt
,   subscription.id 
FROM 
    payment
    INNER JOIN 
        subscription 
        ON payment.subscription_id = subscription.id
    INNER JOIN 
        user 
        ON user.id = subscription.user_id
WHERE 
    (payment.refunded_date is null) 
    AND (subscription.deleted_at IS NULL)
GROUP BY 
    subscription.id
ORDER BY 
    cnt