Q&A about the site for gardeners and landscapers
declare @maxSpike numeric = ##MaximumOneDaySpike## 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], Count(*) AS [Days over Threshold], Max(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(DAY,p.CreationDate,@endDate) 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.CreationDate, p.OwnerUserId, p.PostTypeId, p.Score ) AS tmp WHERE tmp.singleDaySpike <= @maxSpike GROUP BY tmp.[Post Link], tmp.posttype, tmp.[User Link], tmp.score, tmp.[Age] ORDER BY [Current Score] DESC