1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @TagId AS NVARCHAR(50);
SELECT @TagId = [Id] FROM Tags WHERE TagName = LOWER('##tag##');

DECLARE @Median AS FLOAT;
DECLARE @NegativeScore AS INT;
DECLARE @PositiveScore AS INT;
DECLARE @ZeroScore AS INT;
DECLARE @TotalPosts AS INT;
DECLARE @ClosedPosts AS INT;

SELECT @Median=
(
 (SELECT MAX(Score)
   FROM
   (
     SELECT TOP 50 PERCENT Score
     FROM Posts p,
       PostTags pt
       WHERE p.Id=pt.PostId
         AND pt.TagId=@TagId
         AND p.PostTypeId=1
       ORDER BY Score
   ) AS BottomHalf)
 +
 (SELECT MIN(Score)
   FROM
   (
     SELECT TOP 50 PERCENT Score
       FROM Posts p,
         PostTags pt
       WHERE p.Id=pt.PostId
         AND pt.TagId=@TagId
         AND p.PostTypeId=1
       ORDER BY Score DESC
   ) AS TopHalf)
) / 2;

SELECT @NegativeScore=COUNT(*)
  FROM Posts p,
    PostTags pt
  WHERE p.Id=pt.PostId
    AND pt.TagId=@TagId
    AND p.PostTypeId=1
    AND Score<0;
    
SELECT @PositiveScore=COUNT(*)
  FROM Posts p,
    PostTags pt
  WHERE p.Id=pt.PostId
    AND pt.TagId=@TagId
    AND p.PostTypeId=1
    AND Score>0;
    
SELECT @ZeroScore=COUNT(*)
  FROM Posts p,
    PostTags pt
  WHERE p.Id=pt.PostId
    AND pt.TagId=@TagId
    AND p.PostTypeId=1
    AND Score=0;

SELECT @TotalPosts=COUNT(*)
  FROM Posts p,
    PostTags pt
  WHERE p.Id=pt.PostId
    AND pt.TagId=@TagId
    AND p.PostTypeId=1;
    
SELECT @ClosedPosts=COUNT(*)
  FROM Posts p,
    PostTags pt
  WHERE p.Id=pt.PostId
    AND pt.TagId=@TagId
    AND p.PostTypeId=1
    AND ClosedDate IS NOT NULL;

SELECT
    '##tag##' AS 'Tag Name',
    @Median AS 'Median score',
    AVG(Score+ 0.0) AS 'Mean score',
    VAR(Score) AS 'Variance of score',
    (@NegativeScore+0.0)/@TotalPosts AS 'negative score rate',
    (@PositiveScore+0.0)/@TotalPosts AS 'positive score rate',
    (@ZeroScore+0.0)/@TotalPosts AS 'zero score rate',
    (@ClosedPosts+0.0)/@TotalPosts AS 'close rate'
  FROM Posts p,
    PostTags pt
  WHERE p.Id=pt.PostId
    AND pt.TagId=@TagId
    AND p.PostTypeId=1;

fork query

edited mar 23 23

created mar 23 23 dan1st