0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

SELECT TOP(100) CAST(p.CreationDate AS DATE), 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)
  FROM Posts p 
  INNER JOIN Votes v ON v.PostId = p.Id  AND VoteTypeId in(2,3)
  WHERE p.PostTypeId = 1
  AND p.CreationDate BETWEEN '2021-03-01' AND '2021-04-09'
  GROUP BY CAST(p.CreationDate AS DATE)
  ORDER BY CAST(p.CreationDate AS DATE)

view current version

edited apr 9 21

created apr 9 21 Erik A