Votes by month and bit length of the age of the posts

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

WITH Raw AS (
  SELECT
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1) AS VoteMonth,
    DATEDIFF(day, PostsWithDeleted.CreationDate, Votes.CreationDate)
      AS VoteAge,
    COUNT(*) AS Count
  FROM PostsWithDeleted, Votes
  WHERE
    PostsWithDeleted.PostTypeId <= 2
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
    AND
    Votes.PostId = PostsWithDeleted.Id
    AND
    DATEDIFF(d, PostsWithDeleted.CreationDate, Votes.CreationDate) >= 0
  GROUP BY
    DATEFROMPARTS(
      YEAR(Votes.CreationDate),
      MONTH(Votes.CreationDate),
      1),
    DATEDIFF(day, PostsWithDeleted.CreationDate, Votes.CreationDate)
),
Raw2 AS (
  SELECT
    VoteMonth,
    CASE
      WHEN VoteAge = 0 THEN -1
      ELSE FLOOR(LOG(VoteAge) / LOG(2))
    END AS VoteAgeGroup,
    SUM(Count) AS Count
  FROM Raw
  GROUP BY
    VoteMonth,
    CASE
      WHEN VoteAge = 0 THEN -1
      ELSE FLOOR(LOG(VoteAge) / LOG(2))
    END
),
Raw3 AS (
  SELECT
    VoteMonth,
    SUM(Count) AS Count
  FROM Raw2
  GROUP BY VoteMonth
),
Raw4 AS (
  SELECT
    Raw2.VoteMonth,
    Raw2.VoteAgeGroup,
    100.0 *
      SUM(Raw2.Count) OVER (
        PARTITION BY Raw2.VoteMonth
        ORDER BY Raw2.VoteAgeGroup
        ROWS UNBOUNDED PRECEDING
      )
      / Raw3.Count AS Pct
  FROM Raw2, Raw3
  WHERE
    Raw2.VoteMonth = Raw3.VoteMonth
)
SELECT
  VoteMonth,
  CASE
    WHEN VoteAgeGroup = -1 THEN 'Same day'
    WHEN VoteAgeGroup = 0 THEN 'Same or next day'
    ELSE
      'Lesser than '
        + CAST(POWER(2, VoteAgeGroup + 1) AS VARCHAR(MAX))
        + ' days'
  END AS VoteAgeGroupDesc,
  Pct
FROM Raw4
ORDER BY VoteMonth DESC, VoteAgeGroup

Enter Parameters

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