4

Please login or register to vote for this query.

(click on this box to dismiss)

Average votes of answers for users with at least MinAnswers answers

Q&A about the site for professional robotic engineers, hobbyists, researchers and students

-- Average number of votes per question
SELECT 
  Round(Cast(Votes AS Float) / Posts, 2) AS [Votes],
  Round(Cast(Upvotes AS Float) / Posts, 2) AS [Upvotes],
  Round(Cast(Downvotes AS Float) / Posts, 2) AS [Downvotes],
  Round(Cast(Upvotes AS Float) / Downvotes, 2) AS [Up/Down ratio]
FROM
(
SELECT
  Count(DISTINCT v.PostId) AS [Posts],
  Count(*) AS [Votes],
  Count(CASE v.VoteTypeID WHEN 2 THEN 1 END) AS [Upvotes],
  Count(CASE v.VoteTypeID WHEN 3 THEN 1 END) AS [Downvotes]
FROM 
  Votes v
JOIN 
  Posts p ON v.PostId=p.Id
WHERE 
  p.PostTypeId = ##POSTTYPE## and
  p.ClosedDate is null and 
  ( 
    DATEPART(YYYY, p.CreationDate) > ##AfterYYYY##  or 
    (
      DATEPART(YYYY, p.CreationDate) = ##AfterYYYY## and 
      DATEPART(MM, p.CreationDate) >= ##AfterMM##
    )
  ) and
    ( 
    DATEPART(YYYY, p.CreationDate) < ##BeforeYYYY##  or 
    (
      DATEPART(YYYY, p.CreationDate) = ##BeforeYYYY## and 
      DATEPART(MM, p.CreationDate) < ##BeforeMM##
    )
  ) and
  ( 
    v.VoteTypeId = 2 or
    v.VoteTypeId = 3
  )
) AS Data

view current version

edited may 13 13 Kaveh

created jul 13 10 sam.saffron