0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

SELECT
  CONCAT('site://q/',A.Id,'/11107541|',A.Id) AS PostLink,
  COUNT(F.Id) AS FlagCount,
  MAX(B.BountyAmount) AS BountyAmount,
  MAX(A.CreationDate) AS AnswerDate,
  MAX(B.CreationDate) AS BountyCloseDate,
  MIN(F.CreationDate) AS EarliestFlagViewDate--,
--  STRING_AGG(N.Body, ';') AS PostNotices
FROM Votes B -- for bounty close
LEFT JOIN Votes F ON F.PostId = B.PostId AND F.VoteTypeId = 15 AND F.Id >= 261040086 -- for mod flag view
--LEFT JOIN PostNotices N ON N.PostId = B.PostId
JOIN PostsWithDeleted A ON A.Id = B.PostId
WHERE
      B.VoteTypeId = 9 -- bounty close
  AND B.Id >= 261040036
  AND A.PostTypeId = 2 -- answer
  AND A.Id >= 74621540
  AND A.DeletionDate IS NOT NULL
  AND A.Score >= 3 AND DATEDIFF(d, A.CreationDate, A.DeletionDate) >= 60 -- rep kept despite deletion
GROUP BY A.Id, B.Id
ORDER BY A.Id ASC

fork query

edited may 19

created may 17 anonymous