0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for enthusiasts and scholars of mythology and folklore

WITH questsByTags AS (
    SELECT DISTINCT
                q.Id
                , q.OwnerUserId
    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  -- questions
    AND (
            t.TagName   = 'accessibility'
    )
),
answersByTags AS (
    SELECT
                a.Id
                , a.OwnerUserId
    FROM        Posts a
    INNER JOIN  questsByTags qbt  ON qbt.Id = a.ParentId
),
commntsByTags AS (
    SELECT
                c.Id
                , c.UserId  AS [OwnerUserId]
    FROM        Comments c
    INNER JOIN (
        SELECT              Id FROM questsByTags
        UNION ALL SELECT    Id FROM answersByTags
    ) AS allPosts
    ON allPosts.Id = c.PostId
),
allUsers AS (
    SELECT          OwnerUserId FROM questsByTags
    UNION SELECT    OwnerUserId FROM answersByTags
    UNION SELECT    OwnerUserId FROM commntsByTags
)
SELECT      au.OwnerUserId      AS [User Link]
            , (SELECT Count (qbt.Id) FROM questsByTags  qbt WHERE qbt.OwnerUserId = au.OwnerUserId)  AS [Num Qsts]
            , (SELECT Count (abt.Id) FROM answersByTags abt WHERE abt.OwnerUserId = au.OwnerUserId)  AS [Num Ans]
            , (SELECT Count (cbt.Id) FROM commntsByTags cbt WHERE cbt.OwnerUserId = au.OwnerUserId)  AS [Num Cmmnts]
FROM        allUsers au
WHERE       au.OwnerUserId IS NOT NULL
ORDER BY    [Num Qsts] DESC, [Num Ans] DESC, [Num Cmmnts] DESC

fork query

edited nov 27 anonymous

created apr 25 18 Brock Adams