Posts per Tag with stats in date range

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

--- Calculate our sums, for each tag

SELECT Counts.TagName as Tag, TotalPosts, TotalAnswers, TotalComments, TotalFavs, UnansweredCount
FROM
(SELECT TagName, COUNT(PostId) as TotalPosts, SUM(AnswerCount) as TotalAnswers, SUM(CommentCount) as TotalComments, SUM(FavoriteCount) as TotalFavs
  FROM Tags, PostTags, Posts
  WHERE
    Tags.id = PostTags.TagId and Posts.Id = PostId
    AND CreationDate >= ##StartDate:string##
    AND CreationDate <= ##EndDate:string##
    AND TagName in (##Tags:string##)
    GROUP BY TagName) as Counts

INNER JOIN

--- Find all unanswered posts, using the same criteria.
--- The query looks basically the same, but the difference is the 'AnswerCount'

(SELECT TagName, COUNT(PostId) as UnansweredCount
  FROM Tags, PostTags, Posts
  WHERE
    Tags.id = PostTags.TagId and Posts.Id = PostId
    AND CreationDate >= ##StartDate:string##
    AND CreationDate <= ##EndDate:string##
    AND TagName in (##Tags:string##)
    AND AnswerCount = 0
    GROUP BY TagName) as UnansweredCounts

--- Do the INNER JOIN matching the TagNames

ON Counts.TagName = UnansweredCounts.TagName

Enter Parameters

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