0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people interested in improving and participating in the patent system

declare @window numeric = 30

DECLARE @endDate date
SELECT  @endDate = max(CreationDate) from Posts;

SELECT TOP 1000
    (CASE tmp.posttype
        WHEN 1 THEN 'Q'
        WHEN 2 THEN 'A'
     END),
    tmp.[Post Link],
    tmp.[User Link],
    tmp.[Age],
    tmp.score AS [Current Score],
    SUM(tmp.dVotes) AS [Recent Score],
    MAX(tmp.dVotes) AS [Vote Spike],
    Cast(STDEVP(tmp.dVotes) AS DECIMAL(20,3)) AS [StdDev]
FROM

-- Subquery forked from:
-- http://data.stackexchange.com/stackoverflow/query/108188/upvotes-per-post-and-per-day
    (SELECT
      p.PostTypeId AS posttype,
      v.PostId AS [Post Link],
      p.OwnerUserId AS [User Link],
      v.CreationDate AS [Date],
      DATEDIFF(DAY,p.CreationDate,@endDate) AS [Age],
      Count(*) AS dVotes,
      p.score AS score
    FROM Votes v
    LEFT JOIN Posts p ON p.Id = v.PostId
    WHERE
        DATEDIFF(DAY,v.CreationDate,@endDate) < @window and
        v.VoteTypeId = 2 and p.PostTypeId is not null
    GROUP BY
        v.PostId,
        v.CreationDate,
        p.CreationDate,
        p.OwnerUserId,
        p.PostTypeId,
        p.Score
    ) AS tmp
    
GROUP BY
    tmp.[Post Link],
    tmp.posttype,
    tmp.[User Link],
    tmp.score,
    tmp.[Age]
ORDER BY [StdDev] DESC

view current version

edited apr 20 13

created apr 20 13 anonymous