rep league wip

0

Please login or register to vote for this query.

(click on this box to dismiss)

EOS.IO

Q&A for users and developers of distributed applications built on top of the blockchain operating system, EOS.IO

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
     , u.reputation
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 u.reputation > 200
-- and p.communityowneddate is null
-- select 


-- 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 meta site
loading Hold tight while we fetch your results