SUM for multiple condition with 2 tables in one query

So I would like to get the first and last name and the count of viewed players present in column Viewed_PlayerID of table ViewedPlayers of each user, whose userID is present in column CreatedByCharacterID of table ViewedPlayers. Of which the viewed_playerID is marked as "teamplayer" in column UserType of table Users.

Table Users:

Table ViewedPlayers:

Expected result:

I tried 2 different queries

Query #1:

SELECT      
    c.Firstname, c.LastName,
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) AS Count
FROM 
    Users c
JOIN    
    ViewedPlayers p ON p.CreatedByCharacterID = c.ID
WHERE   
    c.UserType = 'Teamplayer'
GROUP BY 
    p.CreatedByCharacterID
ORDER BY 
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) DESC

Query #2:

SELECT      
    c.Firstname, c.LastName,
    SUM(CASE WHEN c. UserType = 'Teamplayer' AND p.CreatedByCharacterID THEN 1 ELSE 0 END) AS Count
FROM    
    Users c
JOIN
    ViewedPlayers p ON p.CreatedByCharacterID = c.ID
GROUP BY 
    p.CreatedByCharacterID
ORDER BY 
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) DESC

Your query just needs two separate references to the Users table. One to get the viewer's name, and one to get the viewee's UserType.

SELECT
    u.Firstname,
    u.LastName,
    COUNT(*) 
FROM
    Users           AS u
INNER JOIN
    ViewedPlayers   AS vp
        ON vp.CreatedByCharacterID = u.ID
INNER JOIN
    Users           AS p
        ON vp.Viewed_PlayerID      = p.ID
WHERE
    p.UserType = 'Teamplayer'
GROUP BY
    u.Firstname,
    u.LastName
ORDER BY
   COUNT(*) DESC