-- Using parameters with DECLARE (for ad-hoc queries in SSMS)
DECLARE @userName NVARCHAR(100) = 'someUsername'
DECLARE @isStepDeleted BIT = 0
DECLARE @isProcessActivated BIT = 1

SELECT 
    s.process_id,
    bp.name AS process_name,
    s.step_id,
    s.name AS step_name,
    s.step_type,
    CASE 
        WHEN s.definition_json LIKE '%"dn":"' + @userName + '"%' THEN @userName
        ELSE 'Team assignment'
    END AS assigned_to
FROM 
    cf_bp_steps s
JOIN 
    cf_bp_processes p ON s.process_id = p.process_id
JOIN 
    cf_business_processes bp ON p.bp_id = bp.bp_id
WHERE 
    s.is_deleted = @isStepDeleted
    --AND s.step_type = 'userTask'
    AND (
        s.definition_json LIKE '%"dn":"' + @userName + '"%'
    )
    AND bp.is_activated = @isProcessActivated
ORDER BY 
    s.process_id, s.step_id;

I just came up with this today. It grabs service tasks as well as user tasks.