0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

SELECT
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0) AS [Post Month],
  SUM(
    CASE 
      WHEN DATEDIFF(day, p.CreationDate, v.CreationDate) <= 2 
          AND VoteTypeId = 2 
        THEN 1.0 
      WHEN DATEDIFF(day, p.CreationDate, v.CreationDate) <= 2 
          AND VoteTypeId = 3 
        THEN -1.0 
      ELSE 0.0 
    END
  ) / COUNT(DISTINCT PostId) AS [48Hr Votes]
FROM PostsWithDeleted p
INNER JOIN Votes v ON v.PostId = p.Id 
  AND VoteTypeId in (2,3)
WHERE p.PostTypeId = 1
  AND p.CreationDate <= DATEADD(month, -1, GETDATE()) --Don't include current month since it has incomplete data
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0)

fork query

edited apr 9 21 zcoop98

created apr 9 21 Erik A