Post closers and their reputation (special present for @Magisch)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Data Science

Q&A for Data science professionals, Machine Learning specialists, and those interested in learning more about the field

-- JSON PITA Part by Glorfindel, adapted from there
SELECT Closers.PostId AS [Post Link], Users.Id AS [User Link], Users.Reputation

FROM
(
SELECT PostId, JSON_VALUE(Text, '$.Voters[0].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 11
UNION ALL
SELECT PostId, JSON_VALUE(Text, '$.Voters[1].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 11
UNION ALL
SELECT PostId, JSON_VALUE(Text, '$.Voters[2].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 11
UNION ALL
SELECT PostId, JSON_VALUE(Text, '$.Voters[3].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 11
UNION ALL
SELECT PostId, JSON_VALUE(Text, '$.Voters[4].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 11
) AS Closers
-- note that UserId can be NULL, if the question is closed by less than 5 people.
-- a JOIN on Users takes care of that

INNER JOIN Users ON Closers.UserId = Users.Id

WHERE Users.Reputation < 3000

Enter Parameters

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