0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for finance professionals and academics

--SELECT MAX(ID) FROM PostsWithDeleted;
--SELECT MIN(ID) FROM PostsWithDeleted;

--SELECT * FROM PostsWithDeleted WHERE ID BETWEEN 79240666 AND 79240686;
SELECT * FROM PostsWithDeleted WHERE parentid = 4 and deletiondate is null;
--SELECT * FROM PostsWithDeleted WHERE id = 4;
--SELECT Score, CreationDate FROM POSTSWITHDELETED WHERE PARENTID = 4 AND PostTypeId = 2;

SELECT
  --*
  q.ID as [Post Link],
  MIN(a.Score) as LOWEST_ANSWER_SCORE,
  MAX(a.Score) as HIGHEST_ANSWER_SCORE,
  AVG(a.Score) as AVERAGE_ANSWER_SCORE,
  APPROX_PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a.Score) as MEDIAN_ANSWER_SCORE,
  COUNT(q.ID) as TOTAL_NUMBER_OF_ANSWERS,
  q.AnswerCount as VISIBLE_ANSWERS
FROM PostsWithDeleted q
LEFT JOIN PostsWithDeleted a
ON q.ID = a.ParentId
WHERE 1=1
AND q.PostTypeId = 1  --only questions, not answers/comments/etc
AND a.PostTypeId = 2  --only answers
--AND q.ID < 100       --artificially limit ourselves for testing purposes
AND q.ID > 79240000
--AND q.CreationDate > '2024-11-30 23:33:49'
AND q.AnswerCount > 1 --questions whose answer count is greater than 1
AND a.DeletionDate IS NULL
GROUP BY q.ID, q.AnswerCount
ORDER BY q.ID, q.AnswerCount
;

view current version

edited dec 1

created nov 25 davidalayachew