I started to compose this query and I find it's difficult to see why the question should be closed.
select TOP ##Limit:int?38369## -- The maximum value the hardware can handle. Posts.Id as [Post Link], -- Question title. Count(PendingFlags.PostId) as [Number of pending flags], -- Number of pending flags per questions. Posts.OwnerUserId as [User Link], -- Let click on the colum to see if the same user ask off-topic questions often. Reputation as [User Reputation], -- Interesting to see that such questions are sometimes asked by high rep users. Posts.Score as [Votes], -- Interesting to see that some questions have more than 100 upvotes. Posts.AnswerCount as [Number of Answers], -- I thought we shouldn't answer on off- topic post. Posts.FavoriteCount as [Number of Stars], -- Some questions seems to be very helpfull :) . Posts.CreationDate as [Asked on], -- The older is the question, the more is the chance that flags on them can't get reviewed. Posts.LastActivityDate as [last activity], -- Similar effect as with Posts.CreationDate. Posts.LastEditDate as [modified on], Posts.ViewCount from posts LEFT OUTER JOIN Users on Users.id = posts.OwnerUserId INNER JOIN PendingFlags on PendingFlags.PostId = Posts.Id where ClosedDate IS NULL -- The question is not closed. group by Posts.id, Posts.OwnerUserId, Reputation, Posts.Score, Posts.FavoriteCount, Posts.AnswerCount, Posts.CreationDate, Posts.LastActivityDate, Posts.LastEditDate, Posts.ViewCount order by Count(PendingFlags.PostId) desc; -- Questions with more flags have more chance to get them handled, and the higher is the probabilty that the question is off-topic (since several users already reviewed the question).
Given that their are several flags per questions, I can't use a simple table to show the flag the reason used for each flag, but I think it should be relevant to show the most common value of CloseReasonTypes.Id for each post : this lead me to two problems :
First : After looking at this query, I should JOIN CloseReasonTypes to PendingFlags to show the reason names instead of their numbers. Since there are no common field between Posts and PendingFlags, but as I'm using
from postsas the base for joining tables, I have no clue on how to do this JOIN.
Secound : I have no idea to select the most common used close reason on each line. While several question seems to have discussed of similar case, I can't use their answers as they ask on how to find the most common value on a whole table resulting in a table with a single column and a single line, whereas I need to do this for the count of flags on each posts.