Post score distribution after 30 days of post creation, by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Apps

Q&A for apps, scripts, and development with the Stack Exchange API

DECLARE @Until DATETIME;
SELECT @Until = DATEADD(d, -##DaysAfter##-1, MAX(CreationDate))
FROM PostsWithDeleted;
SELECT @Until = DATEFROMPARTS(YEAR(@Until), MONTH(@Until), 1);

WITH Raw AS (
  SELECT
    DATEFROMPARTS(
      YEAR(MIN(PostsWithDeleted.CreationDate)),
      CASE
        WHEN ##Yearly:int?0## = 0
        THEN MONTH(MIN(PostsWithDeleted.CreationDate))
        ELSE 1
      END,
      1) AS Month,
    CASE
      WHEN COUNT(Votes.VoteTypeId) = 0
      THEN 0
      WHEN SUM(5 - 2 * Votes.VoteTypeId) > ##MaxScore:int?3##
      THEN ##MaxScore##
      WHEN SUM(5 - 2 * Votes.VoteTypeId) < ##MinScore:int?-3##
      THEN ##MinScore##
      ELSE SUM(5 - 2 * Votes.VoteTypeId)
    END AS Score
  FROM PostsWithDeleted
  LEFT JOIN Votes ON
    Votes.PostId = PostsWithDeleted.Id
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
    AND
    DATEDIFF(day, PostsWithDeleted.CreationDate, Votes.CreationDate)
      BETWEEN 0 AND ##DaysAfter:int?30##
  WHERE
    PostsWithDeleted.CreationDate < @Until
    AND
    (
      (
        PostsWithDeleted.PostTypeId = 1
        AND
        ##ShowQuestions:int?1## <> 0
      )
      OR
      (
        PostsWithDeleted.PostTypeId = 2
        AND
        ##ShowAnswers:int?1## <> 0
      )
    )
  GROUP BY PostsWithDeleted.Id
),
Raw2 AS (
  SELECT
    Month,
    Score,
    COUNT(*) AS Count
  FROM Raw
  GROUP BY Month, Score
),
Raw3 AS (
  SELECT
    Month,
    SUM(Count) AS Count
  FROM Raw2
  GROUP BY Month
),
Scores AS (
  SELECT DISTINCT Score FROM Raw2
),
Months AS (
  SELECT DISTINCT Month FROM Raw2
),
Raw4 AS (
  SELECT
    Months.Month,
    Scores.Score,
    COALESCE(Raw2.Count, 0) AS ScoreCount,
    COALESCE(Raw3.Count, 0) AS AllCount
  FROM Scores
  LEFT JOIN Months ON 1=1
  LEFT JOIN Raw2
    ON Raw2.Month = Months.Month AND Raw2.Score = Scores.Score
  LEFT JOIN Raw3 ON Raw3.Month = Months.Month
)
SELECT
  Month,
  CASE
    WHEN Score = ##MinScore##
    THEN CAST(##MinScore## AS VARCHAR(MAX)) + ' or less'
    WHEN Score = ##MaxScore##
    THEN CAST(##MaxScore## AS VARCHAR(MAX)) + ' or more'
    ELSE CAST(Score AS VARCHAR(MAX))
  END AS Score,
  100.0 * 
    SUM(ScoreCount) OVER (
      PARTITION BY Month
      ORDER BY Score
      ROWS UNBOUNDED PRECEDING
    ) / AllCount AS Pct
FROM Raw4
ORDER BY Raw4.Month DESC, Raw4.Score DESC

Enter Parameters

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