6

Stack Overflow Trends shows the percentage of questions with a certain tag. Here's an example for Java IDEs. On Jan 7, 2023, this link produces this graph:

enter image description here

So VS Code is at 0.8%, and IntelliJ is slightly above 0.2%.

Now I want to run a Data Explorer Query that gives me the latest values for every tag in that chart. But I can't get the percentages to line up. With the query below (view it in Data Explorer), I calculate the percentages based on December 2022. But I'm off: VS Code is 0.775%, and IntelliJ is 0.192%. What am I doing wrong here?

DECLARE @StartDate nvarchar(20) = '2022-11-30' 
DECLARE @EndDate nvarchar(20) = '2023-01-01'
DECLARE @NumberOfQuestions decimal (20,0)


SET @NumberOfQuestions = (SELECT count(Id)
  FROM Posts
  WHERE PostTypeId = 1 
    AND CreationDate > @StartDate
    AND CreationDate < @EndDate);

SELECT ROUND(COUNT(Posts.id) * 100 / @NumberOfQuestions, 3) AS '%', 'VS Code' AS 'Tag' 
FROM Posts
  INNER JOIN PostTags ON PostTags.PostId = Posts.id
  INNER JOIN Tags ON Tags.id = PostTags.TagID
WHERE TagName = 'visual-studio-code' 
  AND PostTypeId = 1 
  AND CreationDate > @StartDate
  AND CreationDate < @EndDate
  
UNION ALL  

SELECT ROUND(COUNT(Posts.id) * 100 / @NumberOfQuestions, 3) AS '%', 'IntelliJ' AS 'Tag' 
FROM Posts
  INNER JOIN PostTags ON PostTags.PostId = Posts.id
  INNER JOIN Tags ON Tags.id = PostTags.TagID
WHERE TagName = 'intellij-idea' 
  AND PostTypeId = 1 
  AND CreationDate > @StartDate
  AND CreationDate < @EndDate
  
UNION ALL  

SELECT ROUND(COUNT(Posts.id) * 100 / @NumberOfQuestions, 3) AS '%', 'Eclipse' AS 'Tag' 
FROM Posts
  INNER JOIN PostTags ON PostTags.PostId = Posts.id
  INNER JOIN Tags ON Tags.id = PostTags.TagID
WHERE TagName = 'eclipse' 
  AND PostTypeId = 1 
  AND CreationDate > @StartDate
  AND CreationDate < @EndDate
  
UNION ALL  

SELECT ROUND(COUNT(Posts.id) * 100 / @NumberOfQuestions, 3) AS '%', 'NetBeans' AS 'Tag' 
FROM Posts
  INNER JOIN PostTags ON PostTags.PostId = Posts.id
  INNER JOIN Tags ON Tags.id = PostTags.TagID
WHERE TagName = 'netbeans' 
  AND PostTypeId = 1 
  AND CreationDate > @StartDate
  AND CreationDate < @EndDate
;
2
  • 1
    Even if I include deleted posts and tag edits I'm not getting anywhere close to what Trends wants us to believe. The raw data under Trends isn't available either so I can't tell for sure what is off or why. Do note that @StartDate nvarchar(20) = '2022-11-30' gives you an whole extra day because that converts to a date 2022-11-30 00:00:00 UTC when used in And CreationDate > @StartDate.
    – rene
    Jan 7 at 16:13
  • 1
    @rene Good catch with the start date! I fixed it, but the numbers change just slightly: VS Code is now 0.772% (further away from 0.8%), IntelliJ is 0.194% (close to 0.2%). Jan 7 at 23:43

0

You must log in to answer this question.

Browse other questions tagged .