Q&A about the site for administrators, end users, developers and designers for Craft CMS
SELECT q.Id AS QuestionID, q.CreationDate, q.Score AS NetScore, LEN(q.Body) AS BodyLength, -- Approximate measure of word count (LEN(q.Body) - LEN(REPLACE(q.Body, CHAR(10), '')) + 1) AS LineCount, -- Line count based on newline characters (SELECT COUNT(*) FROM Posts AS p WHERE p.ParentId = q.Id) AS CodeBlockCount, (SELECT COUNT(*) FROM Comments AS c WHERE c.PostId = q.Id) AS CommentCount, (SELECT COUNT(*) FROM PostHistory WHERE PostHistory.PostId = q.Id AND PostHistory.PostHistoryTypeId = 5) AS EditCount, (SELECT COUNT(*) FROM Tags AS t JOIN PostTags AS pt ON pt.TagId = t.Id WHERE pt.PostId = q.Id AND t.TagName IN ('algorithm', 'data-structures', 'optimization')) AS TechnicalTagCount, -- Detect advanced tags (SELECT COUNT(*) FROM Votes AS v WHERE v.PostId = q.Id AND v.VoteTypeId = 2) AS Upvotes, (SELECT COUNT(*) FROM Votes AS v WHERE v.PostId = q.Id AND v.VoteTypeId = 3) AS Downvotes FROM Posts AS q WHERE q.PostTypeId = 1 -- Only questions AND q.CreationDate BETWEEN '2023-03-14' AND '2023-03-21' ORDER BY q.CreationDate;