3

I'm trying to aggregate some data for my site to give some context to Shog9's awesome moderation yearly statistics. So I wanted to write my own SEDE query to find out the number of (undeleted) questions, answers, and comments were posted to the site in each year.

This one is supposed to combine everything into one:

SELECT YEAR(p.CreationDate) as Year, COUNT(iif(PostTypeId=1, p.Id, null)) as Questions, COUNT(iif(PostTypeId=2, p.Id, null)) as Answers, SUM(p.CommentCount) as Comments

FROM Posts p

WHERE PostTypeId in (1,2)

GROUP BY YEAR(p.CreationDate);

Whereas this was the previous one I had used to count just comments:

SELECT COUNT(c.Id) as Comments, YEAR(c.CreationDate) as Year_Data
FROM Comments c
GROUP BY YEAR(c.CreationDate);

When I run the scripts I get very different answers for the number of comments. For RPG.se in 2019, for example, the first script gives me 39,583 and the second script gives me 43,289. Why the difference?

As best as I can understand, the first one should count all comments attached to every post on the site, but somehow it gets a lower number than querying the comments table directly, and I can't figure out why.

3
  • 2
    nomination posts maybe? And you can have comments created in 2019 on posts from years earlier
    – rene
    Jan 2, 2020 at 19:03
  • @rene ahhhh that second one seems like you nailed it. I didn't think about that. The query is only counting comments on posts made in 2019, whereas the second is counting all comments made in 2019. Jan 2, 2020 at 19:11
  • 1
    this one should make it more clear where the differences are from: data.stackexchange.com/rpg/query/1173889/…
    – rene
    Jan 2, 2020 at 19:16

1 Answer 1

6

You are comparing apples and oranges.

In the first query you take the creationdate of the post to group on the total number of comments for a post that was created in a certain year.

In the second query you take the creationdate of the comment to group the total number of comments created each year.

I can totally write comments today on a post that was created in 2011.

Here is a query that shows this relation:

SELECT COUNT(c.Id) as Comments
     , YEAR(c.CreationDate) as Year_Data
     , YEAR(p.CreationDate) as Post_Year_Data
     ,  p.posttypeid
FROM Comments c
inner join posts p on p.id = c.postid
GROUP BY YEAR(c.CreationDate)
 , YEAR(p.CreationDate)  
 ,  p.posttypeid

order by 2,3,4

when run today you'll find:

query results 2019

Keep in mind SEDE is updated once a week on Sunday.
Give a big shout-out to Monica Cellio for the awesome SEDE Tutorial she wrote.
Say "Hi" in SEDE chat.

2
  • Interestingly, the (algebraic) sum of the two queries should be the same right? Since the sum of all comments on posts made in each year should be the same as the sum of all the comments made in each year. Interestingly, the sums seem to be slightly different (205,310 vs 205,409). I wonder if your nominations idea is also at play here. Jan 2, 2020 at 19:29
  • 1
    @Rubiksmoose as commentcount is a redundant attribute in Posts I wouldn't be surprised if you'll find differences. I have found them on other cases for sure. But yes, there are 99 comments on Nomination posts (PostTypeId = 6) so that makes it complete.
    – rene
    Jan 2, 2020 at 19:30

You must log in to answer this question.

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