CODEWITHSUNDEEP
mysqlsqldatabasestackexchangedataexplorer
Mike Trpcic
Mike Trpcic

Reputation: 25649

How to get Questions, Answers per tag via data.stackexchange.com?

I apologize if this is the incorrect location for this - please move it if so! I've been trying for the past several hours to come up with a query that will return the following information:

The number of Questions and number of Answers per "Top 5" Tag from the last three completed months (April 2012, May 2012, June 2012).

I've gotten this far, based on other queries I've found:

SELECT *, COUNT(*) AS Count FROM
(
  SELECT CAST(
      CAST(DATEPART(YYYY, CreationDate) AS varchar) + '-' + CAST(DATEPART(MM, CreationDate) AS varchar) + '-01'
    AS DATETIME) [Month], 
    t.TagName
  FROM Posts p 
  JOIN PostTags pt ON pt.PostId = p.Id
  JOIN Tags t ON t.Id = pt.TagId
  WHERE TagId IN 
  (
    SELECT TOP 5 Id FROM Tags 
    ORDER BY Count DESC
  )
) AS X
WHERE [Month] >= CAST('2012-04-01' as datetime)
AND [Month] < CAST('2012-07-01' as datetime)
GROUP BY TagName, [Month]
ORDER BY [Month] ASC, TagName ASC

The issue is that the JOIN eliminates records that don't have associated PostTags. I am not extremely skilled with SQL, so this has caused me some problems. Relevent Information:

If anyone can help I would be extremely appreciative.

Thanks.

Upvotes: 2

Views: 134

Answers (1)

PHL
PHL

Reputation: 21

Five months later, I think this does what you want:

https://data.stackexchange.com/stackoverflow/query/89629/questions-per-tag-top-five-by-month

Providing counts for the answers complicates the query a bit, but I think the query above assigns both questions and answers into the proper months and tags.

Upvotes: 1

Related Questions