0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in statistics, machine learning, data analysis, data mining, and data visualization

SELECT 
    TagUsage.TagName,
    TagUsage.NumQuestions,
    TagUsage.CumulativeCount,
    TotalPosts.TotalPostCount,
    (TagUsage.CumulativeCount * 1.0 / TotalPosts.TotalPostCount) PercentageOfTotal
FROM 
    (
        SELECT 
            Tags.TagName,
            COUNT(PostTags.PostId) NumQuestions,
            SUM(COUNT(PostTags.PostId)) OVER (ORDER BY COUNT(PostTags.PostId) DESC) CumulativeCount
        FROM 
            Tags
            INNER JOIN PostTags ON Tags.Id = PostTags.TagId
            INNER JOIN Posts ON Posts.Id = PostTags.PostId
        WHERE 
            Posts.PostTypeId = 1 -- Only consider questions
        GROUP BY 
            Tags.TagName
    ) TagUsage,
    (
        SELECT COUNT(Id) TotalPostCount
        FROM Posts
        WHERE PostTypeId = 1 -- Only consider questions
    ) TotalPosts
WHERE 
    (TagUsage.CumulativeCount * 1.0 / TotalPosts.TotalPostCount) <= 0.90
ORDER BY 
    TagUsage.NumQuestions DESC;

fork query

edited aug 14

created aug 14 anonymous