Most significant tags by percentage

0

Please login or register to vote for this query.

(click on this box to dismiss)

Sports

Q&A for participants in team and individual sport activities

WITH evalUsageRatio as (
  SELECT TagName, Count, Count / (
    SELECT CAST(COUNT(Id) AS float) FROM posts WHERE posttypeid = 1
  ) AS UsageRatio
  FROM Tags
),
evalCumulatedUsageRatio as (
  SELECT TagName, Count, UsageRatio, (
    SELECT SUM(UsageRatio)
    FROM evalUsageRatio
  ) AS CumulatedUsageRatio
  FROM evalUsageRatio
  GROUP BY TagName, Count, UsageRatio
),
evalUsagePercentage as (
  SELECT TagName, Count, (100 * UsageRatio / CumulatedUsageRatio) AS UsagePercentage
  FROM evalCumulatedUsageRatio
),
evalCumulativeUsagePercentage as (
  SELECT TagName, Count, UsagePercentage, SUM(UsagePercentage) OVER (ORDER BY Count desc) AS CumulativeUsagePercentage
  FROM evalUsagePercentage
)/*,
mostUsedTagNames as (
  SELECT TagName
  FROM evalCumulativeUsagePercentage
  WHERE CumulativeUsagePercentage <= 75
)
*/
SELECT Id, Tags, CONCAT(Title, ' ### ', Body) AS Question
FROM Posts
WHERE PostTypeId = 1
  AND Title is not null
  AND Body is not null
  AND Tags IN (
    SELECT TagName
    FROM evalCumulativeUsagePercentage
    WHERE CumulativeUsagePercentage <= 75
  )
--ORDER BY RAND()

Enter Parameters

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