One Vote Short

19

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

Ask Ubuntu

Q&A for Ubuntu users and developers

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 ('Nice Question',  70, 1,  9,  0, 0, 0, 0, 0, 0, 0)
insert into #conditions values ('Nice Answer',    75, 2,  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],
       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 (     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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results