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 about the site for developers and end users of the Sitecore CMS and multichannel marketing software

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],
       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

view current version

edited jun 16 14

created feb 21 14 rolfl