1

Please login or register to vote for this query.

(click on this box to dismiss)

How many unique users have posted questions and/or answers for a given set of tags? Note that deleted posts are not in the database and that there is no way to distinguish one deleted user from another.

Q&A for professional and enthusiast programmers

-- Tag1: Tag name (lowercase) (required) "Enter a tag name (lowercase)"
-- Tag2: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA or a single space"
-- Tag3: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA or a single space"
-- Tag4: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA or a single space"
DECLARE @Tag1 varchar(255)  = ##Tag1:string?javascript##;
DECLARE @Tag2 varchar(255)  = ##Tag2:string?NA##;
DECLARE @Tag3 varchar(255)  = ##Tag3:string?NA##;
DECLARE @Tag4 varchar(255)  = ##Tag4:string?NA##;

WITH postUserByTag AS (
    SELECT
                q.ID,
                1               AS postType,  -- Question
                q.OwnerUserId   AS userID
    FROM        Posts           q
    INNER JOIN  PostTags        pt
    ON          q.Id            = pt.PostId
    INNER JOIN  Tags            t
    ON          t.Id            = pt.TagId
    WHERE       q.PostTypeId    = 1
    AND         t.TagName       IN (@Tag1, @Tag2, @Tag3, @Tag4)

    UNION ALL SELECT
                a.ID,
                2               AS postType,  -- Answer
                a.OwnerUserId   AS userID
    FROM        Posts           a
    INNER JOIN  Posts           q
    ON          q.Id            = a.ParentId
    INNER JOIN  PostTags        pt
    ON          q.Id            = pt.PostId
    INNER JOIN  Tags            t
    ON          t.Id            = pt.TagId
    WHERE       a.PostTypeId    = 2
    AND         t.TagName       IN (@Tag1, @Tag2, @Tag3, @Tag4)
)
SELECT
    COUNT( DISTINCT pu.userID )                                             AS [Unique, Overall, Active Users],
    COUNT( DISTINCT( CASE WHEN  pu.postType = 1  THEN  pu.userID  END ) )   AS [Active Question Askers],
    COUNT( DISTINCT( CASE WHEN  pu.postType = 2  THEN  pu.userID  END ) )   AS [Active Answerers],
    COUNT( DISTINCT(
        CASE WHEN  pu.postType = 1  AND  pu.userID IS NULL  THEN  pu.ID  END
    ) )
    AS [Questions by Deleted Users],
    COUNT( DISTINCT(
        CASE WHEN  pu.postType = 2  AND  pu.userID IS NULL  THEN  pu.ID  END
    ) )
    AS [Answers by Deleted Users]

FROM        postUserByTag   pu

fork query

edited aug 7 15

created aug 7 15 Brock Adams