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:
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
;
@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 inAnd CreationDate > @StartDate
.