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, MAX(A.DeletionDate) AS AnswerDeletionDate--, -- 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