Upvotes I have for each tag is not consistent with what my page says

Question

I came across several queries that calculate the upvotes I have for each tag and found the statistics are not the same as what my own page displays. For some tags, the calculation is less, while some others are more. What's the reason?

Do the queries omit some upvotes? Basically I excluded the possibility that some posts have already been deleted.

Answer 1

The query you're using doesn't calculate accurately the numbers you see in your profile on the Tag page. That is without taking the weekly update into account to explain differences.

These are the changes needed to have a query results that will be correct once a week, at Sunday:

• Only calculate score for Posts that are not community wiki. This is determined whether the CommunityOwnedDate is null.
• Tagscore is upvotes minus downvotes.

I have adapted the query and created this new one that takes the above changes into account to have a column Tag Score that matches what you'll find in a user profile.

Here is it for comparison:

-- How many upvotes do I have for each tag?
-- how long before I get tag badges?

DECLARE @UserId int = ##UserId##
DECLARE @UpvoteTypeId int = 2
DECLARE @DownvoteTypeId int = 3


SELECT --TOP 20 
    TagName,
    COUNT(DISTINCT Posts.Id) AS [Answers],
    COUNT(DISTINCT CASE WHEN Posts.CommunityOwnedDate IS NULL 
              THEN Posts.Id
              ELSE NULL
         END) [Non-CW answers],
    SUM(CASE WHEN votetypeid = @UpvoteTypeId 
             THEN 1 
             ELSE 0 
        END) [Upvotes],
    SUM(CASE WHEN Votes.Votetypeid = @UpvoteTypeId
              AND Posts.CommunityOwnedDate IS NULL 
             THEN 1 
             ELSE 0
        END) [Upvotes non CW],
    SUM(CASE WHEN Votes.Votetypeid = @DownvoteTypeId
             THEN 1 
             ELSE 0 
         END) [Downvotes],
    SUM(CASE WHEN Votes.Votetypeid = @DownvoteTypeId  
              AND Posts.CommunityOwnedDate IS NULL 
             THEN 1 
             ELSE 0 
        END) [Downvotes non CW],
    SUM(CASE WHEN Votes.Votetypeid = @UpvoteTypeId  
              AND Posts.CommunityOwnedDate IS NULL 
             THEN 1 
             ELSE 0 
        END) -
    SUM(CASE WHEN votetypeid = @DownvoteTypeId 
              AND Posts.CommunityOwnedDate IS NULL 
             THEN 1 
             ELSE 0 
        END) [Tag Score]
    
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
    INNER JOIN Votes ON 
              Votes.PostId = Posts.Id  
          AND VoteTypeId IN (@UpvoteTypeId, @DownvoteTypeId)
WHERE 
    Posts.OwnerUserId = @UserId
GROUP BY TagName 
ORDER BY [Tag Score] DESC

And this this what the result looks like for Shog9 on Stack Overflow:

and compare that with his profile page:

Keep in mind SEDE is updated once a week on Sunday.
Use the wonderful SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.