Q&A about the site for administrators and users of the CiviCRM Constituent Relationship Management software
SELECT q.Id AS QuestionId, q.Title AS QuestionTitle, q.Body AS QuestionBody, q.Tags, q.Score AS QuestionScore, q.ViewCount, q.AnswerCount, q.CreationDate AS QuestionCreationDate, CONCAT('https://dba.stackexchange.com/questions/', q.Id) AS QuestionLink, a.Id AS AnswerId, a.Body AS AnswerBody, a.Score AS AnswerScore, a.CreationDate AS AnswerCreationDate, CONCAT('https://dba.stackexchange.com/a/', a.Id) AS AnswerLink FROM Posts q OUTER APPLY ( SELECT TOP 1 * FROM Posts a WHERE a.ParentId = q.Id AND a.PostTypeId = 2 -- Answers AND a.Score >= 0 -- Exclude negatively scored answers AND a.CommunityOwnedDate IS NULL -- Not community wiki AND a.DeletionDate IS NULL -- Not deleted ORDER BY CASE WHEN a.Id = q.AcceptedAnswerId THEN 0 ELSE 1 END, -- Accepted answer first a.Score DESC, -- Then highest score a.CreationDate ASC ) a WHERE q.PostTypeId = 1 -- Questions AND q.ClosedDate IS NULL -- Not closed AND q.CommunityOwnedDate IS NULL -- Not community wiki AND q.DeletionDate IS NULL -- Not deleted AND q.CreationDate >= '2010-09-01' AND q.CreationDate < '2011-09-01' -- Date range AND q.LastEditDate < DATEADD(month, -3, GETDATE()) -- Untouched for at least 3 months AND LEN(q.Body) > 450 -- Questions longer than 450 chars AND q.Score >= 0 -- Non-negative score AND q.AnswerCount BETWEEN 1 AND 10 -- Questions with 1 to 10 answers ORDER BY NEWID()