Posted answers / month by log(rep)

1

Please login or register to vote for this query.

(click on this box to dismiss)

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
    AND
    Posts.OwnerUserId % ##SampleDiv:int?10## = ##SampleRemainder:int?0##
  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
    AND
    OwnerUserId % ##SampleDiv## = ##SampleRemainder##
  GROUP BY
    DATEFROMPARTS(
      YEAR(CreationDate),
      MONTH(CreationDate),
      DAY(CreationDate)),
    OwnerUserId
),

Answer2 AS (
  SELECT
    UserId,
    Date,
    Type,
    AnswerCount,
    SUM(VoteCount) OVER (PARTITION BY UserId ORDER BY Date, Type)
      AS CumVoteCount
  FROM Raw
),
Answer3 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 Answer2
  WHERE Type = 1
),
Answer4 AS (
  SELECT
    Month,
    RepGroup,
    SUM(AnswerCount) AS AnswerNo
  FROM Answer3
  GROUP BY Month, RepGroup
),

Rep2 AS (
  SELECT
    UserId,
    Date,
    SUM(VoteCount) OVER (PARTITION BY UserId ORDER BY Date)
      AS CumVoteCount
  FROM Raw
),
Rep3 AS (
  SELECT
    UserId,
    Date,
    CASE
      WHEN CumVoteCount = 0 THEN 0
      ELSE FLOOR(LOG(CumVoteCount)/LOG(##LogBase:int?3##))
    END AS RepGroup
  FROM Rep2
),
Rep4 AS (
  SELECT
    UserId,
    Date,
    RepGroup,
    LAG(RepGroup) OVER (PARTITION BY UserId ORDER BY Date)
      AS PrevRepGroup
  FROM Rep3
),
Rep5 AS (
  SELECT
    UserId,
    Date,
    CASE
      WHEN PrevRepGroup IS NULL OR PrevRepGroup <> RepGroup
      THEN RepGroup
      ELSE NULL
    END AS NewRepGroup,
    CASE
      WHEN PrevRepGroup IS NOT NULL AND PrevRepGroup <> RepGroup
      THEN PrevRepGroup
      ELSE NULL
    END AS OldRepGroup
  FROM Rep4
),
Rep6 AS (
  SELECT
    DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS Month,
    NewRepGroup AS RepGroup,
    COUNT(*) AS Entered,
    0 AS [Left]
  FROM Rep5
  WHERE NewRepGroup IS NOT NULL
  GROUP BY DATEFROMPARTS(YEAR(Date), MONTH(Date), 1), NewRepGroup
  
  UNION ALL

  SELECT
    DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS Month,
    OldRepGroup AS RepGroup,
    0 AS Entered,
    COUNT(*) AS [Left]
  FROM Rep5
  WHERE OldRepGroup IS NOT NULL
  GROUP BY DATEFROMPARTS(YEAR(Date), MONTH(Date), 1), OldRepGroup
),
Rep7 AS (
  SELECT Month, RepGroup, SUM(Entered) - SUM([Left]) AS Delta
  FROM Rep6
  GROUP BY Month, RepGroup
),
Rep8 AS (
  SELECT
    Month,
    RepGroup,
    SUM(Delta) OVER (PARTITION BY RepGroup ORDER BY Month) AS UserNo
  FROM Rep7
),

Raw2 AS (
  SELECT
    COALESCE(Answer4.Month, Rep8.Month) AS Month,
    COALESCE(Answer4.RepGroup, Rep8.RepGroup) AS RepGroup,
    COALESCE(Answer4.AnswerNo, 0) AS AnswerNo,
    COALESCE(Rep8.UserNo, 0) AS UserNo
  FROM Answer4
  FULL OUTER JOIN Rep8 ON
    Answer4.Month = Rep8.Month
    AND
    Answer4.RepGroup = Rep8.RepGroup
)
SELECT
  Month,
  CASE
    WHEN RepGroup = 0
    THEN '1 - ' + CAST((##LogBase## * 10 - 1) AS VARCHAR(MAX))
    ELSE
      CAST((10 * POWER(##LogBase##, RepGroup)) AS VARCHAR(MAX))
      + ' - '
      + CAST((10 * POWER(##LogBase##, RepGroup + 1) - 1) AS VARCHAR(MAX))
  END + ' Rep' AS RepDesc,
  SUM(1.0 * AnswerNo / UserNo) OVER (PARTITION BY Month ORDER BY RepGroup)
    AS PostPerMonthStackedBar
FROM Raw2
WHERE UserNo > ##MinUserRepGroupMonth:int?1##
ORDER BY Month DESC, RepGroup DESC

Enter Parameters

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