Can someone make a query of number of questions, answers and average upvotes on answers on Earth Science Stack Exchange since its beginning?

Question

Earth Science Stack Exchange is no longer a beta site, but this stats show it is not a healthy site.

My perception is there are a lot of inactive users that started to publish content in the beginning of the site in 2014, but are no longer participating in the site.

I would like to know the evolution of questions and answers, and if possible and if it is easy for you, the number of average upvotes on answers in the site during those years. I would do it myself, but I have no SQL knowledge.

The purpose is to write a meta post encouraging the users to post questions and upvote good answers.

Answer 1

Something like this query to get you going? Basically a PIVOT over a bunch of queries for Questions, Answers, Comment, Accept, up and down votes.

select *
from 
( 
  select 'Q' [type]
     , id
     , eomonth(creationdate) [month]
from posts 
where posttypeid = 1
union all
select 'A' [type]
     , id
     , eomonth(creationdate)
from posts 
where posttypeid = 2
union all
select 'C' [type]
     , id
     , eomonth(creationdate)
from comments
union all 
select 'AV' [type]
     , id
     , eomonth(creationdate)
from votes
where votetypeid = 1
union all 
select 'UV' [type]
     , id
     , eomonth(creationdate)
from votes
where votetypeid = 2
union all 
select 'DV' [type]
     , id
     , eomonth(creationdate)
from votes
where votetypeid = 3
) src
PIVOT 
( 
  count(id) -- what we aggregate on 
  FOR Type  in ([Q], [A], [C], [AV], [UV], [DV])
) pvt
order by [month]

This what the graph looks like:

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.