21

Please login or register to vote for this query.

(click on this box to dismiss)

Select a list of posts which are one-vote away from a badge

Q&A for spacecraft operators, scientists, engineers, and enthusiasts

declare @userid as int = ##UserId:int?-1##
;

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)
;
insert into #conditions values ('Great Question', 10, 1, 99,  0, 0, 0, 0)
insert into #conditions values ('Great Answer',   20, 2, 99,  0, 0, 0, 0)
insert into #conditions values ('Guru',           30, 2, 39,  0, 0, 1, 0)
insert into #conditions values ('Enlightened',    35, 2,  9,  0, 1, 1, 0)
insert into #conditions values ('Necromancer',    40, 2,  4, 60, 0, 0, 0)
insert into #conditions values ('Good Question',  50, 1, 24,  0, 0, 0, 0)
insert into #conditions values ('Good Answer',    60, 2, 24,  0, 0, 0, 0)
insert into #conditions values ('Revival',        63, 2,  1, 30, 1, 0, 0)
insert into #conditions values ('Self-Learner',   67, 2,  2,  0, 0, 0, 1)
insert into #conditions values ('Nice Question',  70, 1,  9,  0, 0, 0, 0)
insert into #conditions values ('Nice Answer',    80, 2,  9,  0, 0, 0, 0)
-- insert into #conditions values ("", , , )
-- insert into #conditions values ("", , , )
    

select ROW_NUMBER() over (order by Rank, u.DisplayName, p.id) as Seq,
       c.Badge,
       -- c.Rank,
       p.OwnerUserId as [User Link],
       u.DisplayName as [User Name],
       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.Score = c.Score
  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 (   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

view current version

edited feb 21 14

created feb 21 14 rolfl