Questions with top 5 tags from 1/10/20 to 10/10/20

0

Please login or register to vote for this query.

(click on this box to dismiss)

Signal Processing Meta

Q&A about the site for practitioners of the art and science of signal, image and video processing

SELECT DISTINCT
p.Id,
Top_Tags.TagName as [Top Tag],
p.Tags,
p.Score,
p.Viewcount,
CASE
  WHEN Accepted_Answer.Id IS NULL THEN NULL
  ELSE DATEDIFF(hour, p.CreationDate, Accepted_Answer.CreationDate)
END AS [Hours to Accepted Answer]

FROM Posts AS p

INNER JOIN PostTypes as ptypes
  ON p.PostTypeId = ptypes.Id
  AND ptypes.Name = 'Question'
  
INNER JOIN PostTags AS ptags
  ON p.Id = ptags.PostId
  
INNER JOIN Tags AS t
  ON t.Id = ptags.TagId
  
INNER JOIN (  

  SELECT TOP 5
  TagName,
  Question_Count

  FROM (
  
    SELECT
    t.TagName,
    COUNT(DISTINCT p.Id) AS Question_Count

    FROM Posts AS p

    INNER JOIN PostTypes as ptypes
      ON p.PostTypeId = ptypes.Id
      AND ptypes.Name = 'Question'

    INNER JOIN PostTags AS ptags
      ON p.Id = ptags.PostId

    INNER JOIN Tags AS t
      ON t.Id = ptags.TagId

    WHERE p.CreationDate BETWEEN '2020-10-01' AND '2020-10-10'

    GROUP BY t.TagName

    ) AS SUB

  ORDER BY Question_Count DESC

) AS Top_Tags
  ON Top_Tags.TagName = t.TagName

LEFT JOIN (

  SELECT 
  p.Id,
  p.CreationDate
  
  FROM Posts AS p
  
  INNER JOIN PostTypes as ptypes
      ON p.PostTypeId = ptypes.Id
      AND ptypes.Name = 'Answer'
      
  WHERE p.CreationDate >= '2020-10-01'
  
) AS Accepted_Answer
  ON p.AcceptedAnswerId = Accepted_Answer.Id

WHERE p.CreationDate BETWEEN '2020-10-01' AND '2020-10-10'

ORDER BY p.Id

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results