Question and answers score history

1

Please login or register to vote for this query.

(click on this box to dismiss)

Get score histories of the question and all of its answers

Biology

Q&A for biology researchers, academics, and students

-- Get score histories of the question and all of its answers

declare @postId int = ##PostID?58408178##
declare @questionType tinyint = 1
declare @postedDate datetime
declare @now datetime = GETDATE()

-- Find question and the associated answer posts, then merge them into 1 table
SELECT p.Id, OwnerUserId, Type, DisplayName as Username, p.CreationDate
INTO #QaPosts
FROM
(
  SELECT Id,
         OwnerUserId,
         CreationDate,
         IIF(PostTypeId = @questionType, 'Q', 'A') AS Type
  FROM Posts
  WHERE Id = @postId

  UNION ALL

  SELECT Id,
         OwnerUserId,
         CreationDate,
         IIF(PostTypeId = @questionType, 'Q', 'A') AS Type
  FROM Posts
  WHERE ParentId = @postId
) AS p
INNER JOIN Users u ON u.Id = p.OwnerUserId


-- Create a table containing all the months since the question was asked to now
SELECT TOP 1 @postedDate = CreationDate
FROM #QaPosts
WHERE Type = 'Q'

SELECT  DATEADD(m, DATEDIFF(m, 0,
          DATEADD(MONTH, x.number, @postedDate)
        ), 0) AS [ReportedMonth]
INTO    #months
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @postedDate, @now);

-- Create a vote history for all posts
SELECT Username,
       p.ID PostID,
       CONCAT(Username, ' ', PostID) PostName,
       Score =
       CASE
         WHEN v.VoteTypeId = 2 THEN 1
         WHEN v.VoteTypeId = 3 THEN -1
         ELSE 0
       END,
       DATEADD(m, DATEDIFF(m, 0, v.CreationDate), 0) [Month]
INTO #Timeline
FROM #QaPosts p
JOIN Votes v on v.PostId = p.Id
WHERE v.VoteTypeId IN (2, 3)

SELECT DISTINCT PostName
INTO #UsersWithPost
FROM #Timeline

DECLARE @columns VARCHAR(MAX) = ''
SELECT @columns = @columns + ',' + QUOTENAME( PostName )
FROM #UsersWithPost
-- [User1], [User2], [User3]
SET @columns = STUFF(@Columns, 1, 1,'')


DECLARE @sums VARCHAR(MAX) = ''
SELECT @sums = @sums + ', COALESCE(SUM(' + QUOTENAME(PostName) + ') over (order by [ReportedMonth]), 0) ' + QUOTENAME(PostName)
FROM #UsersWithPost
-- COALESCE(SUM(User1) over (order by [ReportedMonth]) User1, 0),
-- COALESCE(SUM(User2) over (order by [ReportedMonth]) User2, 0),
-- COALESCE(SUM(User3) over (order by [ReportedMonth]) User3, 0)
SET @sums = STUFF(@sums, 1, 1, '')

DECLARE @sqlText VARCHAR(MAX) = '
-- Transpose. See: https://stackoverflow.com/questions/60906424
SELECT *
INTO #TimelinePivoted
FROM #Timeline 
PIVOT ( SUM(Score) FOR PostName IN ('+ @columns +')) AS PVT

--SELECT * FROM #TimelinePivoted

SELECT ReportedMonth,' + @sums + '
FROM #months m
LEFT JOIN #TimelinePivoted t ON m.ReportedMonth = t.Month
GROUP BY ReportedMonth, '+ @columns +'
'

EXEC (@sqlText)

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results