0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- 1,193,750 when starting              https://web.archive.org/web/20230301153857/https://stackoverflow.com/users/6309/vonc
-- 1,355,584 two days before suspension https://web.archive.org/web/20240423234953/https://stackoverflow.com/users/6309/vonc
DECLARE @T TABLE(URL varchar(50), AcceptRep int, VoteRep int);

INSERT INTO @T
SELECT
  CONCAT('site://q/',P.Id,'|',P.Id) AS URL,
  IIF(Q.AcceptedAnswerId = P.Id, 15, 0) AS AcceptRep,
  SUM(IIF(V.VoteTypeId = 2, 10, 0))+SUM(IIF(V.VoteTypeId = 3, -2, 0)) AS VoteRep
FROM PostsWithDeleted P
JOIN PostsWithDeleted Q ON Q.Id = P.ParentId
LEFT JOIN Votes V ON V.PostId = P.Id
WHERE
  P.Id BETWEEN 75604012 AND 78387665
  AND P.DeletionDate = '2024-04-25 00:00:00'
  AND Q.Tags COLLATE Latin1_General_BIN NOT LIKE '%<git%'
  AND Q.Tags COLLATE Latin1_General_BIN NOT LIKE '%git>%'
  AND P.Score >= 3 AND DATEDIFF(d, P.CreationDate, P.DeletionDate) >= 60 -- rep kept despite deletion
GROUP BY P.Id, Q.AcceptedAnswerId
;

SELECT * FROM @T;

SELECT COUNT(*)*15 FROM @T;

fork query

edited may 3

created may 3 anonymous