Q&A for developers and end users of the Sitecore CMS and multichannel marketing software
CREATE TABLE #Raw ( Score INT, Log2Day INT, Count INT ); WITH Raw AS ( SELECT MIN(PostsWithDeleted.ParentId) AS QId, PostsWithDeleted.Id AS Id, MIN(PostsWithDeleted.CreationDate) AS CreationDate, SUM( CASE WHEN Votes.VoteTypeId = 2 THEN 1 WHEN Votes.VoteTypeId = 3 THEN -1 ELSE 0 END ) AS Score FROM PostsWithDeleted LEFT JOIN Votes ON Votes.VoteTypeId BETWEEN 2 AND 3 AND Votes.PostId = PostsWithDeleted.Id AND DATEDIFF(day, PostsWithDeleted.CreationDate, Votes.CreationDate) BETWEEN 0 AND 30 WHERE PostsWithDeleted.PostTypeId = 2 GROUP BY PostsWithDeleted.Id ), Raw2 AS ( SELECT A.Id, CASE WHEN DATEDIFF(day, Q.CreationDate, A.CreationDate) = 0 THEN 0 ELSE FLOOR(LOG(1.0 * DATEDIFF(day, Q.CreationDate, A.CreationDate)) / LOG(2.0)) END AS Log2Day, CASE WHEN A.Score < ##MinScore:int?-3## THEN ##MinScore## WHEN A.Score > ##MaxScore:int?5## THEN ##MaxScore## ELSE A.Score END AS Score FROM PostsWithDeleted Q, Raw A WHERE A.QId = Q.Id AND DATEDIFF(day, Q.CreationDate, A.CreationDate) BETWEEN 0 AND 32 ) INSERT INTO #Raw SELECT Score, Log2Day, COUNT(*) AS Count FROM Raw2 GROUP BY Log2Day, Score; WITH Raw4 AS ( SELECT Log2Day, SUM(Count) AS Count FROM #Raw GROUP BY Log2Day ) SELECT #Raw.Score, CAST(#Raw.Log2Day AS VARCHAR(MAX)) AS Log2Day, 100.0 * #Raw.Count / Raw4.Count AS Pct FROM #Raw, Raw4 WHERE #Raw.Log2Day = Raw4.Log2Day ORDER BY #Raw.Log2Day, #Raw.Score