Most significant tags by percentage

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping

Q&A for gardeners and landscapers

WITH totalQuestionsNumber as (
  SELECT CAST(COUNT(Id) AS float) as totalQuestionsNumber FROM posts WHERE posttypeid = 1
),
evalUsageRatio as (
  SELECT TagName, Count, Count / totalQuestionsNumber AS UsageRatio
  FROM Tags, totalQuestionsNumber
),
CumulatedUsageRatio as (
  SELECT SUM(UsageRatio) as CumulatedUsageRatio
  FROM evalUsageRatio
),
evalCumulatedUsageRatio as (
  SELECT TagName, Count, UsageRatio, CumulatedUsageRatio
  FROM evalUsageRatio, CumulatedUsageRatio
),
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
)

SELECT DISTINCT /*TOP 5000*/ Id, Tags, CONCAT(Title, ' ### ', Body) AS Question, COUNT(Id)
FROM Posts
WHERE PostTypeId = 1
  AND Title is not null
  AND Body is not null
  AND Tags IN (
    SELECT CONCAT('<', TagName, '>')
    FROM evalCumulativeUsagePercentage
    WHERE CumulativeUsagePercentage <= 75
  )
GROUP BY
    Id, Tags, Title, Body
HAVING 
    COUNT(*) > 1
--ORDER BY RAND()

Enter Parameters

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