I have a above table, and expected output would be

Let me explain we are displaying each user has how many enabled app which are active state.

So lets take the first email id h@gmail.com which returns 1 because this user has 2 app_id associated with it. 2553 and 2554. now in 2553 we wont consider because although it has active it also has an inactive value in it. But for app_id 2554 we have an active enabled_app but there is no inactive value for it. Hence h@gmail.com has count as 1

similarly b@gmail.com has 2 active apps of different app_id.

>Solution :

SELECT
    [user], COUNT([app_id])
FROM
    YourTable
WHERE
    enabled_app = 'active'
    AND [app_id] NOT IN (SELECT [app_id] FROM YourTable WHERE enabled_app = 'inactive')
GROUP BY
    [user]