Monthly count of users by floor(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,
    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
),
Raw2 AS (
  SELECT
    UserId,
    Date,
    SUM(VoteCount) OVER (PARTITION BY UserId ORDER BY Date)
      AS CumVoteCount
  FROM Raw
),
Raw3 AS (
  SELECT
    UserId,
    Date,
    CASE
      WHEN CumVoteCount = 0 THEN 0
      ELSE FLOOR(LOG(CumVoteCount)/LOG(##LogBase:int?3##))
    END AS RepGroup
  FROM Raw2
),
Raw4 AS (
  SELECT
    UserId,
    Date,
    RepGroup,
    LAG(RepGroup) OVER (PARTITION BY UserId ORDER BY Date)
      AS PrevRepGroup
  FROM Raw3
),
Raw5 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 Raw4
),
Raw6 AS (
  SELECT
    DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS Month,
    NewRepGroup AS RepGroup,
    COUNT(*) AS Entered,
    0 AS [Left]
  FROM Raw5
  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 Raw5
  WHERE OldRepGroup IS NOT NULL
  GROUP BY DATEFROMPARTS(YEAR(Date), MONTH(Date), 1), OldRepGroup
),
Raw7 AS (
  SELECT Month, RepGroup, SUM(Entered) - SUM([Left]) AS Delta
  FROM Raw6
  GROUP BY Month, RepGroup
),
Raw8 AS (
  SELECT
    Month,
    RepGroup,
    SUM(Delta) OVER (PARTITION BY RepGroup ORDER BY Month) AS UserNo
  FROM Raw7
)
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(UserNo) OVER (PARTITION BY Month ORDER BY RepGroup DESC)
    AS ColHeight
FROM Raw8
ORDER BY Month DESC, RepGroup

Enter Parameters

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