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],
                SUM(TotalScore * 1.0 / ViewCount) AS [Score Per View]
        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 View] DESC 

Enter Parameters

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