1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for users, experts, and developers of the Tezos blockchain project

WITH DeletableClosed AS (
  SELECT Id AS [Post Link], *
  FROM Posts
  WHERE ClosedDate < DATEADD(d, -2, CURRENT_TIMESTAMP)
),
Raw AS (
  SELECT PostId, Text
  FROM PostHistory
  WHERE PostHistoryTypeId = 10
),
Raw2 AS (
  SELECT PostId, JSON_VALUE(Text, '$.Voters[0].Id') As CloserId
  FROM Raw
  UNION ALL
  SELECT PostId, JSON_VALUE(Text, '$.Voters[1].Id') As CloserId
  FROM Raw
  UNION ALL
  SELECT PostId, JSON_VALUE(Text, '$.Voters[2].Id') As CloserId
  FROM Raw
  UNION ALL
  SELECT PostId, JSON_VALUE(Text, '$.Voters[3].Id') As CloserId
  FROM Raw
  UNION ALL
  SELECT PostId, JSON_VALUE(Text, '$.Voters[4].Id') As CloserId
  FROM Raw
),
Closers AS (
  SELECT PostId, CAST(CloserId AS INT) AS CloserId
  FROM Raw2
)
SELECT
  Closers.PostId AS [Post Link],
  Owner.Id AS [User Link],
  Posts.Score,
  Posts.ClosedDate,
  Owner.Reputation AS [Owner Rep]
FROM Closers, Posts, Users AS Closer, Users AS Owner
WHERE
  Posts.Id = Closers.PostId
  AND
  Owner.Id = Posts.OwnerUserId
  AND
  Closers.CloserId = Closer.Id
  AND
  Closer.AccountId = ##SEAccountId:int##
ORDER BY Posts.Id DESC

fork query

edited aug 7

created aug 7 peterh - PLEASE DELETE THIS ACCOUNT