Relative Close counts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

;With Top100Tags As
(Select Top 100 TagId
From PostTags
Group By TagId
Order By Count(Distinct PostTags.PostId))
SELECT TOP 20 
    TagName,
    COUNT(DISTINCT Posts.Id) AS "Closes",
    COUNT(DISTINCT TP.Id) AS "Open" 
FROM Tags
    Inner Join Top100Tags T100 On T100.TagId = Tags.Id
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.Id = PostTags.PostId
    INNER JOIN Posts TP ON Posts.Id = PostTags.PostId
    Inner Join PostHistory PH On Posts.Id = PH.PostId
    Inner Join PostHistoryTypes HT On HT.Id = PH.PostHistoryTypeId
Where
    Posts.PostTypeId=1
    AND HT.Name='Post Closed'
    And TP.PostTypeId=1
    AND TP.ClosedDate Is NULL
    AND TP.DeletionDate Is NULL    
Group By TagName,HT.Name
Order By COUNT(DISTINCT Posts.Id)/(COUNT(DISTINCT TP.Id)+1) Desc

Enter Parameters

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