Total edits in tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @TodaysDate        Date = getdate();
DECLARE @Tag nvarchar(25) = '##Tag##',
        @12MonthsAgo       Date = DATEADD(Month, -12, DATEADD(dd, DATEDIFF(dd, 0, @TodaysDate), 0) ),
        @QuestionEditsInTag int = 0,
        @TotalQuestionEdits int = 0,
        @EditsPerQuestion decimal(10,3) = 0.0,
        @PercentOfTotalEdits decimal(10,3) = 0.0,
        @QuestionsInTag int = 0,
        @QuestionsOnSite int = 0,
        @PercentOfTotalQuestions decimal(10,3) = 0.0;

SELECT
  @QuestionEditsInTag = COUNT(*)
FROM Posts
  INNER JOIN PostTypes on Posts.PostTypeId = PostTypes.Id
  INNER JOIN PostHistory on Posts.id = PostHistory.PostId
  INNER JOIN PostHistoryTypes on PostHistory.PostHistoryTypeId = PostHistoryTypes.Id
  INNER JOIN PostTags ON Posts.ID = PostTags.PostID
  INNER JOIN Tags ON PostTags.TagID = Tags.ID
WHERE
  PostTypes.Name IN ('Question', 'Answer')
  and Posts.PostTypeId = 1
  and PostHistoryTypes.Name IN ('Edit Title', 'Edit Body', 'Edit Tags')
  and Posts.OwnerUserId != PostHistory.UserId
  and Tags.TagName = @Tag
  
SELECT
  @TotalQuestionEdits = COUNT(*)
FROM Posts
  INNER JOIN PostTypes on Posts.PostTypeId = PostTypes.Id
  INNER JOIN PostHistory on Posts.id = PostHistory.PostId
  INNER JOIN PostHistoryTypes on PostHistory.PostHistoryTypeId = PostHistoryTypes.Id
WHERE
  PostTypes.Name IN ('Question', 'Answer')
  and Posts.PostTypeId = 1
  and PostHistoryTypes.Name IN ('Edit Title', 'Edit Body', 'Edit Tags')
  
SELECT
  @QuestionsInTag = count(*)
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
  
SELECT
  @QuestionsOnSite = count(*)
FROM Posts
WHERE Posts.PostTypeId = 1
  
SET @PercentOfTotalEdits = @QuestionEditsInTag / (convert(decimal(10,3), (@TotalQuestionEdits / 100)));
SET @PercentOfTotalQuestions = @QuestionsInTag / (convert(decimal(10,3), (@QuestionsOnSite / 100)));
SET @EditsPerQuestion = ((@QuestionEditsInTag / (convert(decimal(10,3), @QuestionsInTag / 100))) / 100);
  
SELECT @QuestionEditsInTag AS TotalQEditsInTag, 
       @TotalQuestionEdits AS TotalQEditsOnSite,
       @QuestionsInTag AS TotalQsInTag, 
       @QuestionsOnSite AS TotalQsOnSite,
       @EditsPerQuestion AS EditsPerPost,
       
       (CONVERT(varchar, @PercentOfTotalQuestions) + '%') AS ShareOfTotalQuestions,
       (CONVERT(varchar, @PercentOfTotalEdits) + '%') AS ShareOfTotalEdits

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results