Votes on older posts by month


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

SELECT DATEFROMPARTS(Year, Month, 1), Votes--, Answers, 100 * ROUND(Votes * 1.0 / Answers, 2) as Perf
  FROM (
    SELECT *,
      (SELECT COUNT(*) FROM Posts as AllPosts
        AllPosts.OwnerUserId = ##UserId## And
        AllPosts.CreationDate <= DATEFROMPARTS(Year, Month, 1)
      ) AS Answers
      FROM (
        SELECT DATEPART(YYYY, Votes.CreationDate) AS Year, 
          DATEPART(MM, Votes.CreationDate) AS Month, 
          count(*) AS Votes
          FROM Votes
            INNER JOIN Posts on Votes.PostId = Posts.Id
          WHERE Posts.OwnerUserId = ##UserId##
            AND DATEDIFF(DD, Posts.CreationDate, Votes.CreationDate) > 30
          GROUP BY DATEPART(YYYY, Votes.CreationDate), DATEPART(MM, Votes.CreationDate)
      ) as Raw
  ) as Raw
ORDER BY Year, Month

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results