Untitled

WITH active_deals AS (  
    SELECT * FROM crm_deal   
    WHERE stage_semantic_id = 'P'  
),  
won_deals AS (  
    SELECT * FROM crm_deal   
    WHERE stage_semantic_id = 'S'  
)  
SELECT   
    assigned_by_name,  
    COUNT(active_deals.id) AS active_count,  
    COUNT(won_deals.id) AS won_count  
FROM active_deals  
LEFT JOIN won_deals   
    ON active_deals.assigned_by_id = won_deals.assigned_by_id  
GROUP BY assigned_by_name