Yearly stats

1

Please login or register to vote for this query.

(click on this box to dismiss)

LEGO® Answers

Q&A for LEGO® and building block enthusiasts

-- Does not include deleted posts

select year(CreationDate) year,
       sum(iif(Name = 'Question', 1, 0)) questions,
       sum(iif(Name = 'Answer', 1, 0)) answers
from Posts
     join PostTypes pt on pt.Id = PostTypeId
group by year(CreationDate)
order by year(CreationDate);

-- This query _does_ included deleted posts

select year(CreationDate) year,
       sum(iif(Name = 'Question', 1, 0)) total_questions,
       sum(iif(Name = 'Answer', 1, 0)) total_answers
from PostsWithDeleted p
     join PostTypes pt on pt.Id = PostTypeId
group by year(CreationDate)
order by year(CreationDate);

select year(ph.CreationDate) year,
       count(*) Edits
from PostHistory ph
     join Posts p on p.Id = PostId
where PostHistoryTypeId = 5 -- "Edit Body"
      and PostTypeId in (1,2) -- questions and answers
group by year(ph.CreationDate)
order by year(ph.CreationDate);

select year(CreationDate) year,
       count(*) comments
from Comments
group by year(CreationDate)
order by year(CreationDate);

select year(CreationDate) year,
       sum(iif(Name = 'AcceptedByOriginator ', 1, 0)) accepts,
       sum(iif(Name = 'UpMod', 1, 0)) upvotes,
       sum(iif(Name = 'DownMod', 1, 0)) downvotes
from Votes
     join VoteTypes vt on vt.Id = VoteTypeId
group by year(CreationDate)
order by year(CreationDate);

select top 10 
       p.Id as [Post Link],
       ViewCount
from Posts p
     join PostTypes pt on pt.Id = PostTypeId
where Name = 'Question'
and year(CreationDate) = ##year?2015##
order by ViewCount desc;

select top 10 
       p.Id as [Post Link],
       Score
from Posts p
     join PostTypes pt on pt.Id = PostTypeId
where Name = 'Answer'
and year(CreationDate) = ##year##
order by Score desc;

select top 10
       PostId as [Post Link], 
       sum(case when VoteTypeId = 2 then 1 else 0 end) anon_up, 
       sum(case when VoteTypeId = 3 then 1 else 0 end) anon_down,
       sum(case when VoteTypeId = 2 then 1 else 0 end) - 
           sum(case when VoteTypeId = 3 then 1 else 0 end) anon_score
from PostFeedback f inner join Posts p on p.Id = f.PostId
where year(p.CreationDate) = ##year##
      and year(f.CreationDate) = ##year##
group by PostId
order by anon_score desc

select year(CreationDate) year,
       count(*) new_users
from Users
group by year(CreationDate)
order by year(CreationDate);

select month(CreationDate) month,
       count(*) new_users
from Users
where year(CreationDate) = ##year##
group by month(CreationDate)
order by month(CreationDate);

/*

-- N.B.: This dramatically undercounts the number of reviews.
--       See: http://meta.stackexchange.com/questions/208806/please-add-close-flags-duplicate-links-and-review-data-to-the-public-data-dump

select year(CreationDate) year,
       count(*) reviews
from ReviewTasks
where ReviewTaskStateId = 2 -- Completed
group by year(CreationDate)
order by year(CreationDate);

-- */

/*

-- N.B.: There's no way to see views by the year they occured.
--       This queury shows views by the year the question was asked
--       which isn't likely what you want.

select year(CreationDate) year,
       sum(ViewCount * 1.0) views
from Posts
     join PostTypes pt on pt.Id = PostTypeId
where Name = 'Question'
group by year(CreationDate)
order by year(CreationDate);
-- */

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results