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
