Questions which might have been deleted by the OP after getting an answer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics

Q&A for people studying math at any level and professionals in related fields

--only the questions where
--there is exactly answer
--the answer does not have positive score
--we use (a.DeletionDate=q.DeletionDate) to get only answers which were still available when the question was deleted
with questions_cte AS
(
SELECT TOP ##number?500##
  q.Id AS qid, 
  COUNT(*) AS NumAnswers,
  MAX(a.Score) AS AScore,
  q.CreationDate AS created, q.DeletionDate AS deleted
FROM PostsWithDeleted q
INNER JOIN PostsWithDeleted a ON a.ParentId = q.Id
WHERE (q.Deletiondate IS NOT NULL) and (q.PostTypeId =1) and (a.PostTypeId=2) and (a.DeletionDate=q.DeletionDate)
GROUP BY q.Id, q.CreationDate, q.DeletionDate
ORDER BY q.DeletionDate DESC
)

 SELECT 
   qid,
   url = 'site://q/' + CAST(qid AS varchar),
   NumAnswers, AScore, created, deleted
 FROM questions_cte
 WHERE (NumAnswers=1) AND (AScore<=0)

Enter Parameters

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