0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for gardeners and landscapers

declare @minSpike numeric = ##MinimumOneDaySpike##

SELECT top 1000
    (CASE tmp.posttype
        WHEN 1 THEN 'Q'
        WHEN 2 THEN 'A'
     END),
    tmp.[Post Link],
    tmp.score AS [Current Score],
    tmp.singleDaySpike AS [Largest Spike]
FROM
    (SELECT
      p.PostTypeId AS posttype,
      v.PostId AS [Post Link],
      p.OwnerUserId AS "User Link",
      v.CreationDate AS [Date],
      (DateDiff(second, Min(p.CreationDate), v.CreationDate) + 86400) / 86400 AS [Age],
      Count(*) AS singleDaySpike,
      p.score AS score
    FROM Votes v
    LEFT JOIN Posts p ON p.Id = v.PostId
    WHERE v.VoteTypeId = 2 and p.PostTypeId is not null
    GROUP BY
        v.PostId,
        v.CreationDate,
        p.OwnerUserId,
        p.PostTypeId,
        p.Score
    ) AS tmp
    
WHERE tmp.singleDaySpike >= @minSpike
GROUP BY
    tmp.[Post Link],
    tmp.posttype,
    tmp.[User Link],
    tmp.score,
    tmp.singleDaySpike
ORDER BY [Current Score] DESC

fork query

edited apr 17 13 anonymous

created jun 14 12 Gilles