Score progress

1

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- number range, cf. https://stackoverflow.com/a/34994136/4751173
DECLARE @Min INT = 0, @Max INT = ##AgeInDays:int##;
CREATE TABLE #numbers (Number INT);
INSERT INTO #numbers
SELECT TOP (@Max - @Min + 1) @Min - 1 + ROW_NUMBER() OVER(ORDER BY t1.number) 
  FROM master..spt_values t1 
  CROSS JOIN master..spt_values t2;

CREATE TABLE #results (Title NVARCHAR(250), PostId INT, Age INT, Score INT);

WITH ScoresPerDay AS 
(SELECT p.Title, v.PostId,
  SUM(CASE v.VoteTypeId WHEN 2 THEN 1 WHEN 3 THEN -1 ELSE 0 END) AS Score,
  DATEDIFF(DAY, p.CreationDate, v.CreationDate) AS Age
  FROM Votes AS v
  INNER JOIN Posts AS p
    ON v.PostId = p.Id
   AND DATEDIFF(DAY, p.CreationDate, v.CreationDate) <= ##AgeInDays:int##
  WHERE p.Id IN (SELECT VALUE FROM STRING_SPLIT(##PostIDs:string##, ','))
    AND v.VoteTypeId IN (2, 3) -- up, down
  GROUP BY p.Title, v.PostId, DATEDIFF(DAY, p.CreationDate, v.CreationDate)
-- PostIDs: comma-separated list of post IDs
), Questions AS
(SELECT Title, PostId, MAX(Age) AS MaxAge
  FROM ScoresPerDay
  GROUP BY Title, PostId
)
INSERT INTO #results
SELECT q.Title, q.PostId, n.Number, SUM(s1.Score) AS Score
  FROM Questions AS q
  INNER JOIN #numbers AS n
    ON n.Number <= q.MaxAge
  INNER JOIN ScoresPerDay AS s1
    ON q.PostId = s1.PostId
   AND n.Number >= s1.Age
  GROUP BY q.Title, q.PostId, n.Number

-- cf. https://stackoverflow.com/a/38505375/4751173
DECLARE @columns NVARCHAR(MAX) = '';
SELECT @columns = @columns + QUOTENAME(Title) + ',' FROM (SELECT DISTINCT Title FROM #results) AS Temp;
SELECT @columns = SUBSTRING(@columns, 0, LEN(@columns));

EXECUTE('SELECT * FROM (SELECT Title, Score, Age FROM #results) Temp
PIVOT (MAX(Score) FOR Title IN (' + @columns + ')) Temp2');

Enter Parameters

Options:
Switch sites
loading Hold tight while we fetch your results