rep league wip

0

Please login or register to vote for this query.

(click on this box to dismiss)

English Language and Usage Meta

Q&A about the site for linguists, etymologists, and serious English language enthusiasts

declare @cutoffdate datetime = dateadd(##timeframe?yy##, -1,  DATETRUNC(week, getdate()))


select v.postid, count(*) [upmod]
into #upmods
from votes v 
where votetypeid = 2 
and v.creationdate > @cutoffdate
group by v.postid

select v.postid, count(*) [accept]
into #accepts
from votes v 
where votetypeid = 1
and v.creationdate > @cutoffdate
group by v.postid

select v.postid, count(*) [downmod]
into #downmods
from votes v 
where votetypeid = 3
and v.creationdate > @cutoffdate
group by v.postid

select v.postid, sum(bountyamount) [bounty]
into #bounties
from votes v 
where votetypeid = 9
and v.creationdate > @cutoffdate
group by v.postid


select p.id postid, owneruserid
into #posts_users
from posts p
inner join (
select postid
from #accepts
union
select postid
from #upmods
union 
select postid
from #downmods
union
select postid
from #bounties
) v on v.postid = p.id
inner join users u on u.id = p.owneruserid

-- where p.owneruserid is not null and p.communityowneddate is null

-- create index ix_pu on #posts_users(postid, owneruserid)



print  @cutoffdate

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results