Score per view by tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Question tags ordered by score per view

Islam Meta

Q&A about the site for Muslims, experts in Islam, and those interested in learning more about Islam

-- Score per view by tag
-- Question tags ordered by score per view

SELECT  t.TagName, q.*
FROM    Tags t
CROSS APPLY
        (
        SELECT  SUM(ViewCount) AS [Total Views],
                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  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 

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results