1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

DECLARE @TagId INT = (
  SELECT Id FROM Tags WHERE TagName = ##TagName:string##
);

WITH Raw AS (
  SELECT
    CAST(SUBSTRING(spl.value, 1, CHARINDEX(',', spl.value) - 1) AS INT)
      AS SubjectUserId,
    PostHistory.Id,
    PostHistory.PostHistoryTypeId,
    PostHistory.PostId,
    Posts.OwnerUserId AS ObjectUserId
  FROM PostHistory
  CROSS APPLY STRING_SPLIT(Text, ':') spl
  LEFT JOIN Posts ON PostHistory.PostId = Posts.Id
  WHERE
    PostHistory.PostHistoryTypeId IN (10, 12)
    AND
    SUBSTRING(spl.value, 1, 1) BETWEEN '1' AND '9'
),
Raw2 AS (
  SELECT
    SubjectUserId AS UserId,
    COUNT(*) AS CloseDelCount
  FROM Raw, PostTags
  WHERE
    Raw.SubjectUserId <> Raw.ObjectUserId
    AND
    Raw.PostId = PostTags.PostId
    AND
    PostTags.TagId = @TagId
  GROUP BY Raw.SubjectUserId
)
SELECT
  Raw2.UserId AS [User Link],
  Users.Reputation,
  Raw2.CloseDelCount,
  Users.DownVotes
FROM Raw2, Users
WHERE Raw2.UserId = Users.Id
ORDER BY Raw2.CloseDelCount * Users.DownVotes DESC, Users.Id DESC

fork query

edited feb 3

created feb 3 peterh - PLEASE DELETE THIS ACCOUNT