Average Score per Tag

3

Please login or register to vote for this query.

(click on this box to dismiss)

Shows which tags bring in the most viewers

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

-- Top Tags by Median ViewCount
-- See For Median http://stackoverflow.com/q/1342898/275404

SELECT Tags.tagName,
	MedianViews.[Median Views] AS [Median Views],
	avg(Posts.ViewCount) AS [Average Views],
	sum(Posts.ViewCount) AS [Total Views],
	avg(PostScore.Score) AS [Average Score],
	Count(*) AS [Question Count]
FROM Posts
INNER JOIN PostTags
	ON Posts.Id = PostTags.PostId
INNER JOIN Tags
	ON PostTags.TagId = Tags.Id
INNER JOIN (
	SELECT tagName,
		avg(ViewCount) AS [Median Views]
	FROM (
		SELECT Tags.tagName,
			ViewCount,
			ROW_NUMBER() OVER (
				PARTITION BY Tags.tagName ORDER BY ViewCount ASC, Posts.Id ASC
				) AS RowAsc,
			ROW_NUMBER() OVER (
				PARTITION BY Tags.tagName ORDER BY ViewCount DESC, Posts.Id DESC 
				) AS RowDesc
		FROM Posts
		INNER JOIN PostTags
			ON Posts.Id = PostTags.PostId
		INNER JOIN Tags
			ON PostTags.TagId = Tags.Id
		) AS x
	WHERE RowAsc IN (
			RowDesc,
			RowDesc - 1,
			RowDesc + 1
			)
	GROUP BY tagName
	) AS MedianViews
	ON MedianViews.tagName = Tags.tagName
INNER JOIN (Select Votes.PostId, 
                   sum(case VoteTypeId when 2 then 1 else -1 end) as score  
            From Votes
            Where VoteTypeId in (2,3)
            group by Votes.PostId) as PostScore
    ON PostScore.PostId = Posts.Id
GROUP BY Tags.tagName, [Median Views]
having Count(*) >= 20
ORDER BY [Total Views] DESC

Enter Parameters

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