public
Created

  • Download Gist
gistfile1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
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
t.UserId,
(select b.Id from Badges b where b.Name = 'Enlightened'),
(select CreationDate + 7 from Posts where Id = ReasonId),
'granted by sam',
ReasonId
from #Totals t
join
(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'

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.