Answered question closed by himself

1

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

WITH Raw AS (
  SELECT PostId, Id, Text
  FROM PostHistory
  WHERE Id IN (
    SELECT MAX(Id) AS CloseEventId
    FROM PostHistory
    WHERE
      PostHistoryTypeId = 10
      AND
      PostId IN (
        SELECT Id
        FROM Posts
        WHERE ClosedDate IS NOT NULL
      )
    GROUP BY PostId
  )
),
Raw2 AS (
  SELECT PostId, JSON_VALUE(Text, '$.Voters[0].Id') As Voter
  FROM Raw
  UNION
  SELECT PostId, JSON_VALUE(Text, '$.Voters[1].Id') As Voter
  FROM Raw
  UNION
  SELECT PostId, JSON_VALUE(Text, '$.Voters[2].Id') As Voter
  FROM Raw
  UNION
  SELECT PostId, JSON_VALUE(Text, '$.Voters[3].Id') As Voter
  FROM Raw
  UNION
  SELECT PostId, JSON_VALUE(Text, '$.Voters[4].Id') As Voter
  FROM Raw
),
Closed AS (
  SELECT PostId, CAST(Voter AS INT) AS CloserId
  FROM Raw2
  WHERE Voter IS NOT NULL
),
Answered AS (
  SELECT Q.Id AS PostId, A.OwnerUserId AS AnswererId
  FROM Posts Q, Posts A
  WHERE
    A.ParentId = Q.Id
    AND
    Q.ClosedDate IS NOT NULL
    AND
    A.OwnerUserId > 0
)
SELECT TOP 10000
  Closed.PostId AS [Post Link],
  CloserId AS [User Link],
  COUNT(*) OVER (PARTITION BY CloserId) AS Count
FROM Closed, Answered
WHERE
  Closed.PostId = Answered.PostId
  AND
  Closed.CloserId = Answered.AnswererId
ORDER BY Count DESC, CloserId, Closed.PostId

Enter Parameters

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