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 @Tag nvarchar(25) = '##Tag##',
        @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
join PostTypes on Posts.PostTypeId = PostTypes.Id
join PostHistory on Posts.id = PostHistory.PostId
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
join PostTypes on Posts.PostTypeId = PostTypes.Id
join PostHistory on Posts.id = PostHistory.PostId
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 TotalQuestionEditsInTag, 
       @TotalQuestionEdits as TotalEditsOnSite, 
       (CONVERT(varchar, @PercentOfTotalEdits) + '%') as ShareOfTotalEdits,
       (CONVERT(varchar, @PercentOfTotalQuestions) + '%') as ShareOfTotalQuestions,
       @QuestionsInTag as TotalQuestionsInTag, 
       @QuestionsOnSite as TotalQuestionOnSite,
       @EditsPerQuestion as EditsPerPost

Enter Parameters

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