Community tag acceptance ranking with threshold and search

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

DECLARE @tagQuestionCountThreshold INT;
SET @tagQuestionCountThreshold = ##TagQuestionCountThreshold##;

DECLARE @tagCount INT;
SELECT
  @tagCount = COUNT(1)
FROM
  [Tags]
WHERE
  [Count] > @tagQuestionCountThreshold

SELECT
  [TagName]
 ,CONVERT(NVARCHAR, [Rank]) + ' OF ' + CONVERT(NVARCHAR, @tagCount)
 ,[Percent unacceptable]
FROM
(
SELECT
  ROW_NUMBER() OVER(ORDER BY (CONVERT(DECIMAL, COALESCE([c].[Number], 0.01)) / CONVERT(DECIMAL, [o].[Number])) * CONVERT(DECIMAL, 100) ASC) [Rank]
 ,[o].[TagName]
 ,(CONVERT(DECIMAL, COALESCE([c].[Number], 0.001)) / (CONVERT(DECIMAL, [o].[Number]) + CONVERT(DECIMAL, COALESCE([c].[Number], 0.001)))) * CONVERT(DECIMAL, 100) [Percent unacceptable]
FROM
(
  SELECT
    [t].[TagName]
   ,COUNT(1) [Number]
  FROM
    [Posts] [p]
  INNER JOIN
    [PostTags] [pt] ON [p].[Id] = [pt].[PostId]
  INNER JOIN
    [Tags] [t] ON [pt].[TagId] = [t].[Id]
  WHERE
    [p].[PostTypeId] = 1
    AND [t].[Count] > @tagQuestionCountThreshold
    AND [p].[ClosedDate] IS NULL
    AND [p].[DeletionDate] IS NULL
    AND [p].[Score] > 0
  GROUP BY
    [t].[TagName]
) [o]
LEFT OUTER JOIN
(
  SELECT
    [t].[TagName]
   ,COUNT(1) [Number]
  FROM
    [Posts] [p]
  INNER JOIN
    [PostTags] [pt] ON [p].[Id] = [pt].[PostId]
  INNER JOIN
    [Tags] [t] ON [pt].[TagId] = [t].[Id]
  WHERE[p].[PostTypeId] = 1
    AND [t].[Count] > @tagQuestionCountThreshold
    AND ([p].[Score] < 1 OR [p].[ClosedDate] IS NOT NULL OR [p].[DeletionDate] IS NOT NULL)
  GROUP BY
    [t].[TagName]
) [c] ON [o].[TagName] = [c].[TagName]
) [t]
WHERE [TagName] LIKE '%##TagToSearchFor##%'
ORDER BY [Percent unacceptable] ASC

Enter Parameters

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