3

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.

3
  • 4
    The data explorer's data is only updated once a week. It's not in real time. – Mysticial Nov 29 '13 at 2:16
  • 2
    That query also doesn't take Community Wiki into account. Those are excluded from the count on your profile. – Stijn Oct 29 '14 at 13:44
  • I have seen here several questions about the same things - it is easy to miss that CW-posts are not counted: Explain tag numbers in profile? and Wrong number associated with specific Tag. – Martin Oct 25 '20 at 15:10
3

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:

tags for shog9 with per tag scores

and compare that with his profile page:

shog9 tag pofile page showing per tag score and post count

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .