Votecounts on first questions in the first 30 days before and after reviewer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Webmasters

Q&A for pro webmasters

Declare @DaysSinceReviewer as int
Declare @Start as date
Declare @End as date
SELECT @DaysSinceReviewer =  DateDiff(d, '07/11/2011',  MAX(CreationDate)) from posts 
SELECT @Start = DateAdd(d, (@DaysSinceReviewer * -1) + 30, '07/11/2011')
SELECT @End =  DateAdd(d, -30,  MAX(CreationDate)) from posts 

PRINT @Start
Print @End

;With FirstQuestions as 
(
SELECT MIN(ID) ID, OwnerUSerID
FROM posts p
WHERE postTypeID =2 
and p.creationDate between @Start and @end
GROUP By OwnerUSerID),
First30DayVoteCounts as 
(
SELECT
      CASE WHEN p.creationDate  < '07/11/2011' THEN 'PreReviewerBadge'
           ELSE 'PostReviewerBadge'
      END Type,
      p.Id,
      COUNT(v.id) votes
      
FROM

  FirstQuestions fq
  INNER JOIN posts p
  ON fq.id = p.id
  INNER JOIN votes v
  ON p.id = v.postID
  and DateDiff(d, p.CreationDate, v.CreationDate) <=30
  
GROUP BY
      CASE WHEN p.creationDate  < '07/11/2011' THEN 'PreReviewerBadge'
           ELSE 'PostReviewerBadge'
      END ,
      p.Id
      )
      SELECT TYPE, avg(CAST(votes as DECIMAL (15,4))) as avg_votes,
      STDEV(CAST(votes as DECIMAL (15,4))) STDEV_VOTES
      FROM First30DayVoteCounts
      GROUP BY TYPE

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results