Count of answers posted by estimated log(rep) of the user (at time of posting)

1

Please login or register to vote for this query.

(click on this box to dismiss)

If was hard to make it working even on the SO.

Drupal Answers

Q&A for Drupal developers and administrators

DECLARE @now DATETIME = (SELECT MAX(CreationDate) FROM PostsWithDeleted);

SET @now = DATEFROMPARTS(YEAR(@now), MONTH(@now), 1);

WITH Raw AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    Votes.CreationDate AS Date,
    0 AS Type,
    0 AS AnswerCount,
    CASE WHEN COUNT(*) > 20 THEN 20 ELSE COUNT(*) END AS VoteCount
  FROM Votes, Posts
  WHERE
    Votes.PostId = Posts.Id
    AND
    Posts.OwnerUserId > 0
    AND
    Votes.VoteTypeId = 2
    AND
    Votes.CreationDate < @now
  GROUP BY Posts.OwnerUserId, Votes.CreationDate

  UNION ALL

  SELECT
    OwnerUserId AS UserId,
    DATEFROMPARTS(
      YEAR(CreationDate),
      MONTH(CreationDate),
      DAY(CreationDate)) AS Date,
    1 AS Type,
    COUNT(*) AS AnswerCount,
    0 AS VoteCount
  FROM Posts
  WHERE OwnerUserId > 0 AND CreationDate < @now
  GROUP BY
    DATEFROMPARTS(
      YEAR(CreationDate),
      MONTH(CreationDate),
      DAY(CreationDate)),
    OwnerUserId
),
Raw2 AS (
  SELECT
    UserId,
    Date,
    Type,
    AnswerCount,
    SUM(VoteCount) OVER (PARTITION BY UserId ORDER BY Date, Type)
      AS CumVoteCount
  FROM Raw
),
Raw3 AS (
  SELECT
    CASE
      WHEN CumVoteCount = 0 THEN 0
      ELSE FLOOR(LOG(CumVoteCount)/LOG(##LogBase:int?3##))
    END AS RepGroup,
    AnswerCount,
    DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS Month
  FROM Raw2
  WHERE Type = 1
),
Raw4 AS (
  SELECT
    Month,
    RepGroup,
    SUM(AnswerCount) AS AnswerNo
  FROM Raw3
  GROUP BY Month, RepGroup
)
SELECT
  Month,
  CASE
    WHEN RepGroup = 0 THEN '0 - 19'
    ELSE
      CAST((10 * POWER(##LogBase##, RepGroup)) AS VARCHAR(MAX))
      + ' - '
      + CAST((10 * POWER(##LogBase##, RepGroup + 1) - 1) AS VARCHAR(MAX))
  END + ' Rep' AS RepDesc,
  SUM(AnswerNo) OVER (PARTITION BY Month ORDER BY RepGroup DESC)
    AS ColHeight
FROM Raw4
ORDER BY Month DESC, RepGroup

Enter Parameters

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