Most significant tags by percentage


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
    Id, Tags, Title, Body
    COUNT(*) > 1

Enter Parameters

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