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 :-) – Franck Dernoncourt 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. – Franck Dernoncourt 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 .