Select a list of posts which are one-vote away from a badge
Q&A for professional and enthusiast programmers
declare @tagname as NVarchar(60) = ##Tag:string?all##; declare @tagid as int = null; if lower(@tagname) <> 'all' begin select @tagid = id from Tags where Lower(TagName) = lower(@tagname) end create table #conditions ( Badge NVarChar(40) not null, Rank int not null, PostTypeId int not null, Score int not null, DayDiff int not null, First int not null, Accepted int not null, Selfie int not null, BadQuestion int not null, GoodAnswer int not null, Populist int not null) ; insert into #conditions values ('Populist', 1, 2, 0, 0, 0, 0, 0, 0, 0, 1) insert into #conditions values ('Reversal', 5, 1, -4, 0, 0, 0, 0, 0, 1, 0) insert into #conditions values ('Reversal', 6, 2, 19, 0, 0, 0, 0, 1, 0, 0) insert into #conditions values ('Great Answer', 15, 2, 99, 0, 0, 0, 0, 0, 0, 0) insert into #conditions values ('Great Question', 20, 1, 99, 0, 0, 0, 0, 0, 0, 0) insert into #conditions values ('Guru', 30, 2, 39, 0, 0, 1, 0, 0, 0, 0) insert into #conditions values ('Good Answer', 40, 2, 24, 0, 0, 0, 0, 0, 0, 0) insert into #conditions values ('Good Question', 50, 1, 24, 0, 0, 0, 0, 0, 0, 0) insert into #conditions values ('Enlightened', 60, 2, 9, 0, 1, 1, 0, 0, 0, 0) insert into #conditions values ('Necromancer', 65, 2, 4, 60, 0, 0, 0, 0, 0, 0) insert into #conditions values ('Revival', 80, 2, 1, 30, 1, 0, 0, 0, 0, 0) insert into #conditions values ('Self-Learner', 85, 2, 2, 0, 0, 0, 1, 0, 0, 0) -- insert into #conditions values ("", , , ) -- insert into #conditions values ("", , , ) ; with Locked as ( select distinct PostId from PostHistory ph where PostHistoryTypeId = 14 and not exists (select 1 from PostHistory sub where sub.PostId = ph.PostId and sub.PostHistoryTypeId = 15 and sub.CreationDate > ph.CreationDate) ) select TOP 8000 p.id as [Post Link], p.CreationDate as [Created], pt.Name as [Post Type], p.OwnerUserId as [User Link], c.Badge from Posts p inner join #conditions c on p.PostTypeId = c.PostTypeId inner join PostTypes pt on p.PostTypeId = pt.Id left outer join Posts parent on p.ParentId = parent.Id where p.id not in (select PostId from Locked) and IsNull(p.ParentId, 0) not in (select PostId from Locked) and exists ( select 1 from PostTags where TagId = IsNull(@tagid, TagId) and PostId = IsNull(p.ParentId, p.Id) ) and ( c.Populist = 0 AND p.Score = c.Score OR c.Populist = 1 AND p.Score > 22 AND parent.id is not null AND p.score != parent.AcceptedAnswerId AND p.Score = 2 * (select Score from Posts where parent.id is not null and id = parent.AcceptedAnswerId and PostTypeId = 2)) and ( DayDiff = 0 OR DayDiff <= DateDiff(day, IsNull(parent.CreationDate, p.CreationDate), p.CreationDate)) and ( Accepted = 0 OR p.Id = IsNull(parent.AcceptedAnswerId, 0)) and ( Selfie = 0 OR ( p.OwnerUserId = IsNull(parent.OwnerUserId, 0) AND not exists (select 1 from badges where UserId = p.OwnerUserId and Name = 'Self-Learner'))) and ( BadQuestion = 0 OR IsNull(parent.Score, 0) <= -5) and ( GoodAnswer = 0 OR exists (select 1 from Posts a where a.ParentId = p.Id and a.PostTypeId = 2 and a.Score >= 20)) and ( First = 0 OR NOT EXISTS (select 1 from Posts prev where prev.ParentId = p.ParentId and prev.CreationDate < p.CreationDate and prev.PostTypeId = p.PostTypeId)) order by p.CreationDate DESC