3

Please login or register to vote for this query.

(click on this box to dismiss)

Shows which tags bring in the most viewers

Q&A about the site for speakers of German wanting to discuss the finer points of the language and translation

-- 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],
	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
GROUP BY Tags.tagName, [Median Views]
having Count(*) >= 20
ORDER BY [Median Views] DESC

view current version

edited mar 2 12 davrob60

created mar 2 12 jon.doe7339