How can I get the total number of posts for a user that have a certain tag in them?

Question

User abbood (myself) has eight posts (in total) that have the tag cocoapods; six questions and two answers.

I thought that this query should simply get all eight:

SELECT Count(*)
FROM   posts p
       JOIN posttags pt
         ON p.id = pt.postid
       JOIN tags t
         ON t.id = pt.tagid
WHERE  owneruserid = 766570
       AND t.tagname = 'cocoapods' 

but instead it only got six.

It turns out that posts that are answers are not associated with tags. So for example this query

SELECT *
FROM   posttags
WHERE  postid IN (SELECT p.id AS 'post id'
                  FROM   posts p
                  WHERE  p.owneruserid = 766570
                         AND p.posttypeid = 1)  // posttypeid = 1 is a question

returns many results, whereas this query

SELECT *
FROM   posttags
WHERE  postid IN (SELECT p.id AS 'post id'
                  FROM   posts p
                  WHERE  p.owneruserid = 766570
                         AND p.posttypeid = 2) 

returns zero results.

How can I get the total number of posts (both questions and answers) for a specific user for a specific tag?

Answer 1

The other answer is correct, but the query can be a lot simpler. Questions and answers are joined by Posts.ParentId - for answers, that's filled with the Posts.Id of the question. So you can simply join Posts with PostTags on the first non-null value of ParentId and Id. You can see it in action here.

SELECT COUNT(*)
  FROM Posts AS p
  INNER JOIN PostTags AS pt
    ON COALESCE(p.ParentId, p.Id) = pt.PostId
  INNER JOIN Tags AS t
    ON t.Id = pt.TagId
  WHERE p.OwnerUserId = ##UserId:int##
    AND t.TagName = '##TagName##'

Answer 2

PostTags are only associated with a question. So for all the questions a user posted in a specific tag, you do a simple join. For the answers, you need to check all of their answers, and then determine which ones belong to a question (usually authored by a different user) that have the same tag. Of course in this step you need to filter out self-answers because you already counted the corresponding question. So:

DECLARE @tagId int; -- simplify later joins!
SELECT @tagId = Id FROM Tags WHERE TagName = N'##TagName##';

SELECT Total = SUM(c) FROM
(
  -- first, get the questions this user wrote in that tag
  SELECT c = COUNT(*)
  FROM Posts AS p
  INNER JOIN PostTags AS pt
          ON p.Id = pt.PostId
  WHERE p.OwnerUserId = ##UserId##
    AND p.PostTypeId = 1 -- question
    AND pt.TagId = @tagId
    
  UNION ALL
  
  -- now get their answers to questions on the tag, but filter 
  -- out this user's questions because they're already counted
  SELECT c = COUNT(*)
  FROM Posts AS a
  WHERE a.PostTypeId = 2 -- answer
    AND a.OwnerUserId = ##UserId##
    AND EXISTS
    (
      SELECT 1 FROM Posts AS q
        INNER JOIN PostTags AS pt
           ON q.Id = pt.PostId
        WHERE q.OwnerUserId <> ##UserId##
          AND q.PostTypeId = 1 -- question
          AND pt.TagId = @tagId
          AND q.Id = a.ParentId
    )
) AS subquery;

• Example