Q&A for professional and enthusiast programmers
-- 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); -- */