Answer score histogram after 30 days, grouped by log2(days after question posted)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Sitecore

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

Enter Parameters

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