Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
select *
into #ElightenedReasons
from (select a.OwnerUserId as UserId,
a.Id as ReasonId
from Posts a with (nolock)
where a.PostTypeId = 2
and a.DeletionDate is null
and a.OwnerUserId is not null
and a.Score >= 10
and exists (select 1 /* question owner cannot own accepted answer */
from Posts q with (nolock)
where q.PostTypeId = 1
and q.AcceptedAnswerId = a.Id
and q.OwnerUserId <> a.OwnerUserId)
and a.Id = (select min(firstAnswer.Id) /* answer must be the first one given */
from Posts firstAnswer with (nolock)
where firstAnswer.DeletionDate is null
and firstAnswer.ParentId = a.ParentId)) as X
where X.ReasonId is not null
select UserId, Total = COUNT(*)
into #Totals
from #ElightenedReasons
group by UserId
update t
set Total = t.Total - Actual
from #Totals t
join (select COUNT(*) Actual, u.UserId from Users2Badges u where BadgeId = (select b.Id from Badges b where b.Name = 'Enlightened') group by u.UserId) X
on X.UserId = t.UserId
delete #Totals
where Total < 1
delete e
from #ElightenedReasons e
join Users2Badges u on BadgeId = (select b.Id from Badges b where b.Name = 'Enlightened') and e.ReasonId = u.ReasonId
delete from #ElightenedReasons
where UserId not in (select t.UserId from #Totals t)
insert Users2Badges
(UserId, BadgeId, Date, Comment, ReasonId)
(select b.Id from Badges b where b.Name = 'Enlightened'),
(select CreationDate + 7 from Posts where Id = ReasonId),
'granted by sam',
from #Totals t
(SELECT ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY ReasonId) AS ROWID, * FROM #ElightenedReasons) X on t.UserId = X.UserId
where ROWID < Total
drop table #Totals
drop table #ElightenedReasons
Update Messages set IsRead = 1
where MessageTypeId = 5
and Text = 'You''ve earned the "Enlightened" badge.'
and CreationDate between '2010-30-09' and '2010-2-09'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment