1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

-- The query contains hardcoded moderatorship intervals, thus
-- it works correctly only on the serverfault (and serverfault meta).

WITH p AS (
  SELECT JSON_VALUE(Text, '$.Voters[0].Id') AS Voter, Id, CreationDate
  FROM PostHistory
  WHERE PostHistoryTypeId IN (10, 11)

  UNION

  SELECT JSON_VALUE(Text, '$.Voters[1].Id') AS Voter, Id, CreationDate
  FROM PostHistory
  WHERE PostHistoryTypeId IN (10, 11)

  UNION

  SELECT JSON_VALUE(Text, '$.Voters[2].Id') AS Voter, Id, CreationDate
  FROM PostHistory
  WHERE PostHistoryTypeId IN (10, 11)

  UNION

  SELECT JSON_VALUE(Text, '$.Voters[3].Id') AS Voter, Id, CreationDate
  FROM PostHistory
  WHERE PostHistoryTypeId IN (10, 11)

  UNION

  SELECT JSON_VALUE(Text, '$.Voters[4].Id') AS Voter, Id, CreationDate
  FROM PostHistory
  WHERE PostHistoryTypeId IN (10, 11)),

Votes AS (
  SELECT
    Voter,
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1) AS Month,
    Id,
    CASE
      WHEN Voter = 3038 AND CreationDate > '2011-02-11' THEN 1
      WHEN Voter = 7079 AND CreationDate > '2011-02-11' THEN 1
      WHEN Voter = 32986 AND CreationDate > '2012-01-24' THEN 1
      WHEN Voter = 33417
        AND CreationDate > '2012-01-24'
        AND CreationDate < '2014-05-02'
      THEN 1
      WHEN Voter = 9157
        AND CreationDate > '2012-01-24'
        AND CreationDate < '2015-12-19'
      THEN 1
      WHEN Voter = 16732
        AND CreationDate > '2013-05-13'
        AND CreationDate < '2015-04-20'
      THEN 1
      WHEN Voter = 126632 AND CreationDate > '2013-05-13' THEN 1
      WHEN Voter = 8897 AND CreationDate > '2014-12-02' THEN 1
      WHEN Voter = 118258
        AND CreationDate > '2014-12-02'
        AND CreationDate < '2015-05-11'
      THEN 1
      WHEN Voter = 1375 AND CreationDate > '2015-09-08' THEN 1
      WHEN Voter = 37681 AND CreationDate > '2015-09-08' THEN 1
      WHEN Voter = 20815 AND CreationDate > '2015-09-08' THEN 1
      ELSE 0
    END AS IsMod
  FROM p
  WHERE Voter > 1 AND CreationDate >= '2011-02-01'),

ModdedVote AS (
  SELECT Month, COUNT(*) AS N
  FROM Votes
  WHERE IsMod = 1
  GROUP BY Month),

AllVote AS (
  SELECT Month, COUNT(*) AS N
  FROM Votes
  GROUP BY Month)

SELECT AllVote.Month, AllVote.N AS AllVotes, ModdedVote.N AS ModdedVotes
FROM ModdedVote, AllVote
WHERE ModdedVote.Month = AllVote.Month
ORDER BY AllVote.Month

fork query

edited mar 27 17

created mar 27 17 peterh