DECLARE @minscroe int = ##minscore##; DECLARE @firstgolds...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Quality Assurance and Testing Meta

Q&A about the site for software quality control experts, automation engineers, and software testers

DECLARE @minscroe int = ##minscore##;
DECLARE @firstgolds int = ##firstgolds##;

--DECLARE @UserId int = ##UserId##

with gposts as( select postid, creationdate
from (
select postid, creationdate,
          row_number() over (partition by postid order by creationdate) as rnk
   from votes
   where votetypeid=2
) t
where rnk = @minscroe),
firstng as( select year2, days
from (
select datediff(d, p.creationdate, gp.creationdate) as days, 
 year(p.creationdate) as year2,
          row_number() over (partition by year(p.creationdate) order by p.creationdate) as rnk2
   from gposts gp inner join posts p on gp.postid = p.id
) t2
where rnk2 <= @firstgolds)
select year2 as [year], avg(days) as days from firstng
group by year2
having count(*)=@firstgolds
order by year2
return;
--firstn as (select year(p.creationdate) as [year], 
--avg(datediff(d, p.creationdate, gp.creationdate)) as days,
--count(*) numgold
--from gposts gp inner join posts p on gp.postid = p.id
--group by year(p.creationdate)
--order by year(p.creationdate)

--select year(p.creationdate) as [year], 
--avg(datediff(d, p.creationdate, gp.creationdate)) as days,
--count(*) numgold
--from gposts gp inner join posts p on gp.postid = p.id
--group by year(p.creationdate)
--order by year(p.creationdate)

Enter Parameters

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