Average Views Per Question In A Specified Tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Graphic Design

Q&A for Graphic Design professionals, students, and enthusiasts

DECLARE @TotalPosts int = 0,
        @TotalViews int = 0,
        @ViewsPerPost decimal(10,4) = 0.0,
        @Tag nvarchar(25) = '##Tag##';

CREATE TABLE #PostsInTag (
   PostID int,
   ViewCount int
);

INSERT INTO #PostsInTag
SELECT Posts.Id, Posts.ViewCount
FROM Posts
  INNER JOIN PostTags 
  ON Posts.ID = PostTags.PostID
  INNER JOIN Tags 
  ON PostTags.TagID = Tags.ID
WHERE Posts.PostTypeId = 1
  AND Tags.TagName = @Tag
  AND Posts.ClosedDate is null

SELECT @TotalPosts = COUNT(PostID), @TotalViews = SUM(ViewCount)
FROM #PostsInTag

SET @ViewsPerPost = @TotalViews / @TotalPosts

SELECT @TotalPosts AS [Number of Questions], 
       @TotalViews AS [Number Of Views], 
       @ViewsPerPost AS [Average Views Per Post]

Enter Parameters

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