0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

-- Total Posts in 2014

select count(Id) from Posts
where
  PostTypeId IN (1, 2) and
  CreationDate BETWEEN '2013-10-1' and '2013-12-1'
  
-- Downvoted in 2014

Select count(distinct down.PostId) from Votes as down
join Posts on 
  down.PostId = Posts.Id
where
  down.VoteTypeId = 3 and
  Posts.CreationDate BETWEEN '2013-10-1' and '2013-12-1'
  
-- Downvoted -> Commented in 2014

Select count(distinct down.PostId) from Votes as down
join Comments on
  down.PostId = Comments.PostId and
  Comments.CreationDate > down.CreationDate
join Posts on 
  down.PostId = Posts.Id
where
  down.VoteTypeId = 3 and
  Posts.CreationDate BETWEEN '2013-10-1' and '2013-12-1'
  
-- Downvoted -> Edited in 2014

Select count(distinct down.PostId) from Votes as down
join PostHistory on
  down.PostId = PostHistory.PostId and
  PostHistory.CreationDate > down.CreationDate
join Posts on 
  down.PostId = Posts.Id
where
  PostHistory.UserId = Posts.OwnerUserId and
  down.VoteTypeId = 3 and
  PostHistory.PostHistoryTypeId = 5 and
  Posts.CreationDate BETWEEN '2013-10-1' and '2013-12-1'

-- Downvoted -> Edited -> Upvoted in 2014

Select count(distinct down.PostId) from Votes as down
join PostHistory on
  down.PostId = PostHistory.PostId and
  PostHistory.CreationDate > down.CreationDate
join Votes as up on
  up.PostId = down.PostId and
  up.CreationDate > PostHistory.CreationDate
join Posts on 
  down.PostId = Posts.Id
where
  PostHistory.UserId = Posts.OwnerUserId and
  up.VoteTypeId = 2 and
  down.VoteTypeId = 3 and
  PostHistory.PostHistoryTypeId = 5 and
  Posts.CreationDate BETWEEN '2013-10-1' and '2013-12-1'
  
-- Downvoted -> Commented -> Edited in 2014

Select count(distinct down.PostId) from Votes as down
join Comments on
  down.PostId = Comments.PostId and
  Comments.CreationDate > down.CreationDate
join PostHistory on
  down.PostId = PostHistory.PostId and
  PostHistory.CreationDate > Comments.CreationDate
join Posts on 
  down.PostId = Posts.Id
where
  PostHistory.UserId = Posts.OwnerUserId and
  down.VoteTypeId = 3 and
  PostHistory.PostHistoryTypeId = 5 and
  Posts.CreationDate BETWEEN '2013-10-1' and '2013-12-1'

-- Downvoted -> Commented -> Edited -> Upvoted in 2014

Select count(distinct down.PostId) from Votes as down
join Comments on
  down.PostId = Comments.PostId and
  Comments.CreationDate > down.CreationDate
 join PostHistory on
  down.PostId = PostHistory.PostId and
  PostHistory.CreationDate > Comments.CreationDate
 join Votes as up on
  up.PostId = down.PostId and
  up.CreationDate > PostHistory.CreationDate
join Posts on 
  down.PostId = Posts.Id
where
  PostHistory.UserId = Posts.OwnerUserId and
  up.VoteTypeId = 2 and 
  down.VoteTypeId = 3 and
  PostHistory.PostHistoryTypeId = 5 and
  Posts.CreationDate BETWEEN '2013-10-1' and '2013-12-1'

fork query

edited mar 6 14 jmac

created mar 4 14 jon.doe13179