1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for apps, scripts, and development with the Stack Exchange API

WITH postAnonFeedback AS (
    SELECT
                q.Id
                , SUM (CASE WHEN pf.VoteTypeId = 2 THEN 1 ELSE 0 END)   AS Helpful
                , SUM (CASE WHEN pf.VoteTypeId = 3 THEN 1 ELSE 0 END)   AS Unhelpful
    FROM        Posts           q
    INNER JOIN  PostTags        pt      ON q.Id = pt.PostId
    INNER JOIN  Tags            t       ON t.Id = pt.TagId
    LEFT JOIN   PostFeedback    pf      ON q.Id = pf.PostId
    WHERE       q.PostTypeId    = 1  -- Questions
    AND         t.TagName       IN ('script', 'app', 'library')
    AND         NOT EXISTS (
                    SELECT      pt2.PostId
                    FROM        PostTags        pt2
                    INNER JOIN  Tags            t2
                    ON          t2.Id           = pt2.TagId
                    WHERE       pt2.PostId      = q.Id
                    AND         t2.TagName      = 'placeholder'
                )
    GROUP BY    q.Id
)
SELECT
            q.Id                AS [Post Link]
            , q.Score
            , paf.Unhelpful     AS [Anon Bad]
            , paf.Helpful       AS [Anon Useful]
            , q.CreationDate
            , q.Tags
FROM        Posts               q
INNER JOIN  postAnonFeedback    paf     ON q.Id = paf.Id
ORDER BY    q.Score
            , paf.Unhelpful DESC
            , paf.Helpful
            , q.Title

fork query

edited jul 30 18

created jul 30 18 Brock Adams