0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for members of the workforce navigating the professional setting

DECLARE @from_date DATETIME = '2023-02-08T00:00:00'
DECLARE @to_date DATETIME = '2023-02-15T00:00:00'

SELECT 
  q.Id AS [Post Id],
  q.CreationDate,
  q.Title,
  q.Body AS [Context],
  q.OwnerUserId AS [User Id],
  q.Score AS [# of Votes],
  q.AnswerCount AS [# of Answers],
  q.ViewCount AS [# of Views],
  STRING_AGG(t.TagName, ', ') AS Tags,
  STRING_AGG(a.OwnerUserId, ', ') AS [Answerer IDs],
  STRING_AGG(a.OwnerDisplayName, ', ') AS [Answerer Names],
  c.Id AS [Comment Id],
  c.Text AS [Comment Text]
FROM dbo.Posts q
INNER JOIN dbo.PostTags pt ON q.Id = pt.PostId
INNER JOIN dbo.Tags t ON pt.TagId = t.Id
LEFT JOIN dbo.Posts a ON q.Id = a.ParentId AND a.PostTypeId = 2
LEFT JOIN dbo.Comments c ON q.Id = c.PostId
WHERE 
  q.PostTypeId = 1
  AND q.CreationDate >= @from_date 
  AND q.CreationDate < @to_date
  AND c.CreationDate >= @from_date 
  AND c.CreationDate < @to_date 
GROUP BY q.Id, q.CreationDate, q.Title, q.Body, q.OwnerUserId, q.OwnerDisplayName, q.Score, q.AnswerCount, q.ViewCount, c.Id, c.Text
ORDER BY q.CreationDate DESC
OFFSET 0 ROWS
FETCH NEXT 50000 ROWS ONLY;

fork query

edited apr 1

created mar 31 anonymous