Answers over the past X Days

0

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

declare @limit int = ##Limit##;
declare @days int = ##Days##;

create table #windowstags (
tagid int,
tagname varchar(30) )

create table #displaynames (
displayname nvarchar(40) )

insert into #displaynames 
values('Chris Bowen - MSFT'),
('devhammer'),
('Joe Healy'),
('taraew'),
('Jared Bienz - MSFT'),
('Jeff Brand - MSFT'),
('Jennifer Marsman - MSFT'),
('Jeremy Foster'),
('Jerry Nixon - MSFT'),
('Jim O''Neil'),
('JP Alioto');

insert into #windowstags (tagid, tagname)
select id, tagname 
from tags 
where tagname  
in ('windows-8', 'microsoft-metro', 'metro-style-app', 'windows-store-apps')

select top (@limit)
rank() over (order by sum(answers.score)) "Rank"
  ,#displaynames.displayname
  ,'http://stackoverflow.com/users/' + CAST(users.id as varchar(25)) as "User Link"
  ,sum(answers.score) as "Score"
  ,count(answers.id) as "Answer Count"
  ,sum(answers.viewcount) as "View Count"
  ,count(comments.id) as "Comment Count"
from #displaynames
join users on ( users.displayname COLLATE DATABASE_DEFAULT
=  #displaynames.displayname COLLATE DATABASE_DEFAULT)
join posts answers on (users.id = answers.owneruserid)
left join comments on (answers.id = comments.postid)

where(answers.posttypeid = 2  -- answers only
and answers.creationdate BETWEEN dateadd(day, -@days, getdate()) AND getdate())
group by users.id, #displaynames.displayname
order by sum(answers.score) desc
;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results