Most significant tags by percentage

0

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla

Q&A for Joomla! administrators, users, developers and designers

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
)

SELECT TagName FROM evalCumulativeUsagePercentage
WHERE CumulativeUsagePercentage <= 75
ORDER BY Count desc

Enter Parameters

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