0

Please login or register to vote for this query.

(click on this box to dismiss)

Question tags ordered by score per thousand views

Q&A for pro webmasters

-- Score per Thousand Views, by tag
-- Question tags ordered by score per thousand views

SELECT  t.TagName, q.*
FROM    Tags t
CROSS APPLY
        (
        SELECT  COUNT(*) AS [Total Questions],
                SUM(ViewCount) AS [Total Views],
                SUM(TotalAnswers) AS [Total Answers],
                SUM(TotalScore) AS [Total Answers Score],
                CAST(SUM(TotalScore) * 1000.0 / SUM(ViewCount) AS DECIMAL(8, 2)) AS [Score Per Thousand Views]
        FROM    PostTags pt
        JOIN    Posts pq
        ON      pq.Id = pt.PostId
                AND pq.PostTypeId = 1
        CROSS APPLY
                (
                SELECT  COUNT(*) AS TotalAnswers,
                        COALESCE(SUM(Score), 0) AS TotalScore
                FROM    Posts pa
                WHERE   pa.ParentId = pq.Id
                ) q
        WHERE   pt.TagID = t.Id
        ) q
WHERE   Count >= 1000
ORDER BY
        [Score Per Thousand Views] DESC 

fork query

created apr 19 11 Quassnoi