Select a list of posts which are one-vote away from a badge
Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language
declare @userid as int = ##UserId:int?-1##; 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 ('Nice Answer', 70, 2, 9, 0, 0, 0, 0, 0, 0, 0) insert into #conditions values ('Nice Question', 75, 1, 9, 0, 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 1000 ROW_NUMBER() over (order by Rank, DateDiff(week, u.LastAccessDate, CURRENT_TIMESTAMP), p.id desc) as Seq, c.Badge, -- c.Rank, p.OwnerUserId as [User Link], u.DisplayName as [User Name], u.Reputation as [User Rep], p.CreationDate as [Created], DateDiff(week, u.LastAccessDate, CURRENT_TIMESTAMP) as [Weeks], p.id as [Post Link], p.Score, pt.Name from Posts p inner join #conditions c on p.PostTypeId = c.PostTypeId inner join PostTypes pt on p.PostTypeId = pt.Id inner join Users u on p.OwnerUserId = u.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 (p.OwnerUserId = @userid OR @userid < 0) 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 Seq