0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

-- Create a temporary table to store the question IDs
CREATE TABLE #TempQuestions (QuestionId INT);

-- Insert the IDs of questions with the 'github-actions' tag and within the date range
INSERT INTO #TempQuestions (QuestionId)
SELECT DISTINCT p.Id
FROM Posts p
WHERE p.PostTypeId = 1 -- Questions
  AND p.Tags LIKE '%<github-actions>%'
  AND p.CreationDate BETWEEN '2019-11-13' AND '2023-08-31';

-- Retrieve the questions with their respective threads
SELECT
    q.Id AS QuestionId,
    q.Title AS QuestionTitle,
    q.CreationDate AS QuestionCreationDate,
    q.Tags AS QuestionTags,
    CONCAT('https://stackoverflow.com/q/', q.Id) AS QuestionLink,
    u.Id AS UserId,
    u.DisplayName AS UserDisplayName,
    u.Reputation AS UserReputation,
    a.Id AS AnswerId,
    a.CreationDate AS AnswerCreationDate,
    a.Body AS AnswerBody,
    a.Score AS AnswerScore,
    ua.Id AS AnswerUserId,
    ua.DisplayName AS AnswerUserDisplayName,
    ua.Reputation AS AnswerUserReputation
FROM Posts q
JOIN Users u ON q.OwnerUserId = u.Id
LEFT JOIN Posts a ON q.Id = a.ParentId
LEFT JOIN Users ua ON a.OwnerUserId = ua.Id
WHERE q.Id IN (SELECT QuestionId FROM #TempQuestions)
ORDER BY q.CreationDate DESC, a.CreationDate ASC;

-- Drop the temporary table
DROP TABLE #TempQuestions;

fork query

created sep 28 anonymous