2

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 usingfrom 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.

  • Maybe it's just me but it's not clear what you want, do you just want the top flag reason listed for each item? – Taryn Jul 28 '14 at 14:10
  • @bluefeet : A question which have some flags or close votes can have different close reason. I can't create a sub-table, so I can't print all the close reason in a singe row *(I don't want to use multiple ones). My idea is to create a column where each row print the most used flag/close reason on each lines corresponding to the question. So Yes it is basically getting the top close/flag reason for each question. – user2284570 Jul 28 '14 at 14:19
1

I made some slight adjustments to the above query and it should get your started:

;with ReasonsPerPost as
(
  -- get the count of each close/flag reason per post
  select TOP ##Limit:int?38369##
    p.Id,
    f.CloseReasonTypeId,
    cr.Name,
    count(f.CloseReasonTypeId) TotalByCloseReason
  from posts p
  inner join PendingFlags f
    on f.PostId = p.Id
  inner join CloseReasonTypes cr 
    on cr.Id=f.CloseReasonTypeId
  where p.ClosedDate IS NULL -- The question is not closed.
    and f.FlagTypeId in (14,13)
  group by p.id, cr.name, f.CloseReasonTypeId
  order by TotalByCloseReason desc
),
TopPerPost as
(
  -- create a row number to order the results by the close reason totals
  select r.Id,
    r.CloseReasonTypeId,
    r.Name,
    r.TotalByCloseReason,
    row_number() over(partition by r.Id order by TotalByCloseReason desc) seq
  from ReasonsPerPost r
)
select 
  p.Id as [Post Link], -- Question title.
  Count(pf.PostId) as [Number of pending flags], -- Number of pending flags per questions.
  tp.Name TopCloseReason,
  p.OwnerUserId as [User Link], -- Let click on the colum to see if the same user ask off-topic questions often.
  u.Reputation as [User Reputation], -- Interesting to see that such questions are sometimes asked by high rep users.
  p.Score as [Votes], -- Interesting to see that some questions have more than 100 upvotes.
  p.AnswerCount as [Number of Answers], -- I thought we shouldn't answer on off-topic post.
  p.FavoriteCount as [Number of Stars], -- Some questions seems to be very helpfull :) .
  p.CreationDate as [Asked on], -- The older is the question, the more is the chance that flags on them can't get reviewed.
  p.LastActivityDate as [last activity], -- Similar effect as with Posts.CreationDate.
  p.LastEditDate as [modified on],
  p.ViewCount -- How many peoples were too dumb to not close the question.
from posts p
inner join PendingFlags pf
  on pf.PostId = p.Id
left join TopPerPost tp
  on p.id = tp.id
left join Users u
  on u.id = p.OwnerUserId
where tp.seq = 1
group by p.id, tp.Name, p.OwnerUserId, u.Reputation, p.Score, 
  p.FavoriteCount, p.AnswerCount, p.CreationDate, 
  p.LastActivityDate, p.LastEditDate, p.ViewCount
order by [Number of pending flags] desc

The query is available on SEDE. This basically gets the list of the top close/flags per post, then you only select the top value for each of these when you join to your original query.

  • Finally, by looking at the details I would have expected the Off topic reason instead of simply "Off-topic". – user2284570 Jul 30 '14 at 12:38
  • @user2284570 Don't suggest an edit to my answer changing the entire query, you can post your own answer. My query was to get you started. – Taryn Jul 30 '14 at 13:34
  • I didn't just change all your query. If you look more carefully, I just added one line at ReasonsPerPostand replaced cr.Name with IIF(CloseReasonTypeId<>102,CloseReasonTypes.Name,MarkdownMini) as Name,.This is the only thing which change the result. The remaining is written to adapt your coding style to the one used in the question. Just check it. – user2284570 Jul 30 '14 at 13:41
  • @user2284570 If you've created your own version of the query (which you did) then post it as a separate answer. – Taryn Jul 30 '14 at 14:26
  • Yes I did but there's so many bug in it. I'm trying to debug the results myself, but I think I might need help and update my question. – user2284570 Jul 30 '14 at 19:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .