Highest flag/close votes per questions with more control than in the flag queue.

0

Please login or register to vote for this query.

(click on this box to dismiss)

If you want to use this query for flagging, then you'll have problem due to the fact the database isn't updated in real time. It is suitable for things like statistics purposes. Please support http://meta.stackexchange.com/q/257243/242800 if you want closes reasons to appear correctly.

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE PROC dbo.##MARKDOWNtoHTML @text nvarchar(500),
                                 @returnText nvarchar(500) output
AS
BEGIN
    DECLARE @counter tinyint
    SET @counter=1

    WHILE CHARINDEX('**', @text, 1) > 0
    BEGIN
        SELECT @text = STUFF(@text, 
                    CHARINDEX('**', @text, 1), 
                    2, 
                    IIF(@counter%2=0,'</b>','<b>')),
                @counter = @counter + 1
    END
    SET @counter=1
    WHILE CHARINDEX('*', @text, 1) > 0
    BEGIN
        SELECT @text = STUFF(@text, 
                    CHARINDEX('*', @text, 1), 
                    1, 
                    IIF(@counter%2=0,'</i>','<i>')),
                @counter = @counter + 1
    END
    -- SET @returnText = @text
    SET @returnText = @text
END
GO
DECLARE @returnText nvarchar(500);


--from http://stackoverflow.com/a/30447497/2284570
CREATE TABLE #CaRT (akey int, MarkDown nvarchar(800) );
SET NOCOUNT ON;
DEClARE @akey int, @text NVARCHAR(500),@retText NVARCHAR(500);
DECLARE  cur CURSOR LOCAL FAST_FORWARD FOR SELECT id, MarkdownMini 
   FROM CloseAsOffTopicReasonTypes;
OPEN cur;
FETCH NEXT FROM cur into @akey, @text;

WHILE @@FETCH_STATUS=0
BEGIN
   EXEC dbo.##MARKDOWNtoHTML @TEXT, @retText output;
   INSERT INTO #CaRT (akey, MarkDown) values(@akey, @retText);
   FETCH NEXT FROM cur into @akey, @text;
END;

DEALLOCATE cur;
SET NOCOUNT OFF;



--taken from http://meta.stackexchange.com/a/237237/242800
with ReasonsPerPost as
(
   -- get the count of each close/flag reason per post
   select TOP 100 PERCENT -- Only use with clustered index.
      Posts.Id,
      PendingFlags.CloseReasonTypeId,
      IIF(CloseReasonTypeId<>102,CloseReasonTypes.Name,Markdown) as Name,
      count(PendingFlags.CloseReasonTypeId) as TotalByCloseReason
   from Posts
      INNER JOIN PendingFlags on PendingFlags.PostId = Posts.Id
      INNER JOIN CloseReasonTypes on CloseReasonTypes.Id=PendingFlags.CloseReasonTypeId
      LEFT OUTER JOIN #CaRT on #CaRT.akey=PendingFlags.CloseAsOffTopicReasonTypeId
   where Posts.ClosedDate IS NULL -- The question is not closed.
          and PendingFlags.FlagTypeId in (14,13) -- Exclude reopen votes
   group by Posts.id, CloseReasonTypes.Name, Markdown COLLATE SQL_Latin1_General_CP1_CI_AS, PendingFlags.CloseReasonTypeId
   order by TotalByCloseReason desc
),
TopPerPost as
(
  -- create a row number to order the results by the close reason totals
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq
   from ReasonsPerPost
   where Name is NOT NULL
)



select TOP ##Limit:int?38369## -- This number may grow, or get removed the day the server will have enough RAM.
   Posts.Id as [Post Link], -- Question title.
   Count(PendingFlags.PostId) as [Number of pending flags], -- Number of pending flags per questions.
   TopPerPost.Name as [The most common vote reason],
   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.ViewCount,
   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]
from Posts
   INNER JOIN PendingFlags on PendingFlags.PostId = Posts.Id
   LEFT OUTER JOIN Users on Users.id = posts.OwnerUserId
   LEFT OUTER JOIN TopPerPost on Posts.id=TopPerPost.id
where seq=1
group by Posts.id, Posts.OwnerUserId, TopPerPost.Name, Reputation, Posts.Score, Posts.FavoriteCount, Posts.AnswerCount, Posts.CreationDate, Posts.LastActivityDate, Posts.LastEditDate, Posts.ViewCount
order by [Number of pending flags] desc, [The most common vote reason], Score desc, Reputation desc, FavoriteCount desc, ViewCount desc, Posts.CreationDate asc, LastActivityDate, LastEditDate -- 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).

Enter Parameters

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