8

You have already scrolled the Questions page twice, and still looking for other questions to answer?
This script is made for you!

http://data.stackexchange.com/softwarerecs/query/284387/questions-needing-attention

Press Run Query and you will get a list of questions that need to be upvoted/downvoted/answered.

We get about 12 questions per day, and some slip through without getting proper attention … this script can help us do a better job at leaving no question behind.

The data is updated every Monday morning around 3:00 UTC. This is a limitation of Stack Exchange.

5

Some variations:

List posts with no answer, 0 score, fewer than 2 comments, and that haven't been closed:

select CreationDate, OwnerUserId, ViewCount,
  Id as [Post Link], Tags
from Posts
where PostTypeId = 1 
  and Score = 0 
  and AnswerCount = 0
  and CommentCount < 2  
  and ClosedDate IS NULL
order by CreationDate asc

For one given username, list posts with no answer, 0 score, fewer than 2 comments, and that haven't been closed:

select Posts.CreationDate, Posts.OwnerUserId, Users.DisplayName, ViewCount, 
  Posts.Id as [Post Link], Tags
from Posts, Users
where Users.Id = Posts.OwnerUserId 
  and Posts.PostTypeId = 1
  and Posts.Score = 0 
  and Posts.AnswerCount = 0
  and Posts.CommentCount < 2  
  and Posts.ClosedDate IS NULL
  and Users.DisplayName = 'Franck Dernoncourt'
order by Posts.CreationDate asc

Posts that will be deleted in X days, due to the 365 rules (ignoring posts by deleted users):

-- If the question is more than 365 days old, and ...
select Posts.CreationDate, Posts.OwnerUserId, Users.DisplayName, ViewCount, 
  Posts.Id as [Post Link], Tags
from Posts 
     ,Users

where 
  -- joining tables
  Users.Id = Posts.OwnerUserId 

  -- satisfying conditions
  and Posts.PostTypeId = 1 -- is a question
  and Posts.Score <= 0 -- has a score of 0
  and Posts.AnswerCount = 0 -- has no answers
  and Posts.CommentCount < 2  -- has 1 or 0 comments
  and Posts.ViewCount <= DATEDIFF(DAY, Posts.CreationDate, GETDATE()) * 1.5 -- has view count <= the age of the question in days times 1.5
  and DATEDIFF(DAY, Posts.CreationDate, GETDATE()) > (365 - ##WillBedeletedInXDays?10##) -- is old

  -- post is not locked
  and not exists (
        select PostHistory.Id from PostHistory 
        where PostHistory.PostId = Posts.Id
        and PostHistory.PostHistoryTypeId = 14 -- post is locked
  )
order by Posts.CreationDate asc


/*
Schema documentation: https://meta.stackexchange.com/a/2678/178179   
 */

Queries running across all Stack Exchange websites (code mostly taken from rene's SE backup code):

For one given Stack Exchange account ID (e.g., 169656 in my case), list questions with no answer, 0 score, fewer than 2 comments, and that haven't been closed (SEDE link):

IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      -- we have three sites that have borked DNS entries
      set @dbname = (case @dbname
      when 'StackExchange.Audio' then 'StackExchange.Avp'
      when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta'
      when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta'
      else @dbname
      end)
      -- and one of those doesn't want to end with .com
      if @dbname <> 'net.Mathoverflow.meta' 
         set @dbname = 'com.' + @dbname
      exec #recursesiteurl @dbname,  @res OUTPUT
   end
GO
-- create url from dbname
IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #recursesiteurl;
GO
create procedure #recursesiteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)

      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #recursesiteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
GO

declare @accountid int = ##accountid:int##  -- 93484

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_posts ( site nvarchar(250)
                            , id int
                            , title nvarchar(200)
                            , aid int
                            , creationdate datetime
                            , score int
                            , body nvarchar(max)
                            );

open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
               declare @url nvarchar(250)
               exec #siteurl ''' + @db_c_name  + ''', @url output
               insert into #all_posts 
               select @url
               , p.id
               , p.title
               , p.id
               , p.creationdate
               , p.score
               , p.body
               from posts p 
               inner join users pu on pu.id = p.owneruserid 
               where pu.accountid = '+ cast(@accountid as nvarchar) + '
               and 1=1
               and p.PostTypeId = 1
               and p.Score <= 0 
               and p.AnswerCount = 0
               and p.CommentCount < 2  
               and p.ClosedDate IS NULL
               ORDER BY p.CreationDate DESC ;'
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

-- process results
declare db_r cursor for select replace(
       replace(
       replace(site,
         '.StackExchange.com',''),
         '.com',''),
         '.net','') as [site]
     , 'http://' 
     + site 
     + '/q/'
     + cast(id as nvarchar)
     -- + '|'
     -- + title 
       as question
     , title
     , body
from #all_posts


declare @db_r_body nvarchar(max)
declare @db_r_site nvarchar(250)
declare @db_r_title nvarchar(250)
declare @db_r_url nvarchar(250)

open db_r
fetch next from db_r into @db_r_site, @db_r_url, @db_r_title, @db_r_body
while(@@FETCH_STATUS = 0)
begin
   print @db_r_url +'; ' + @db_r_title
   fetch next from db_r into @db_r_site, @db_r_url, @db_r_title, @db_r_body
end;
close db_r;
deallocate db_r;
drop table #all_posts
drop procedure #recursesiteurl
drop procedure #siteurl
5
  • 1
    I can't seem to make a query for the other deletion reasons - if you have time, could you please try? I'm trying to test a userscript I've made for softwarerecs.stackexchange.com/questions/17905/… but I don't know which questions to check on! Mar 29 '15 at 15:16
  • @ᔕᖺᘎᕊ Yeah my end goal of adding variations was to progressively reach a query that matches "other deletion reasons" (listing all questions that are going to be deleted in the next X days). Haven't found the time yet, shouldn't be too hard but tedious :-) Mar 29 '15 at 16:18
  • Too hard for me anyway! Well, if and when you get the time, please tell me, so I can check the script - I don't think I should post it if it's not accurate yet! It works for the links your current query provides, so there's no reason not to work on the others... Mar 29 '15 at 16:19
  • 1
    @ᔕᖺᘎᕊ I've added the query for the 365-day rule. The 30-day rule should have a similar form. Mar 29 '15 at 17:04
  • Great! Script works with them as well. I'll assume (more like hope) that it works with the other deletion process as well, I'll post my script soon :) Mar 29 '15 at 17:40

You must log in to answer this question.

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