Views per vote

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

declare @startDate datetime
set @startDate = '1/1/2009'
 
 
 SELECT MonthCreated, 
 CONVERT(decimal(38, 19), Upvotes+Downvotes) / Convert(decimal(38, 19), Views) as VotesPerView
 FROM
 (
   SELECT 
    DATEADD (month, DATEDIFF (month, 0, p.CreationDate), 0) as [MonthCreated],
    SUM(p.ViewCount) as [Views],
    SUM(IsNull(v.Upvotes, 0)) as [Upvotes],
    SUM(IsNull(v.Downvotes, 0)) as [Downvotes]
    
  FROM Posts p
    LEFT OUTER JOIN 
    (
        SELECT 
        p.Id as PostId,
        SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) as [Upvotes],
        SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) as [DownVotes]
  
      FROM Votes v 
        JOIN Posts p ON (p.Id = v.PostId) AND p.PostTypeId = 1
        
      WHERE (v.VoteTypeId = 2)
         AND p.CreationDate >= @startDate
      GROUP BY p.Id
    ) as V on P.Id = V.PostId
  
  WHERE P.CreationDate >= @startDate
  
  GROUP BY DATEADD (month, DATEDIFF (month, 0, p.CreationDate), 0)
) as T

ORDER BY MonthCreated

Enter Parameters

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