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 experienced flyers, pilots, and builders of drones and model aircraft

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

select TOP 1000
       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 (     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

view current version

edited feb 21 14

created feb 21 14 rolfl