0

Please login or register to vote for this query.

(click on this box to dismiss)

Query to find the fastest users to reach 10K

Q&A about the site for people who build and repair bicycles, people who train cycling, or commute on bicycles

-- Fastest to 10k
-- Query to find the fastest users to reach 10K

if object_id('tempdb..#t') is not null drop table #t
;

create table #t (
owneruserid int,
rn int,
creationdate datetime,
bounty int,
immune int,
votes_capped int,
total as isnull(immune,0) + isnull(votes_capped,0),
primary key clustered(owneruserid, rn))
;

insert #t
select
  owneruserid,
  rn=row_number() over (partition by owneruserid order by creationdate),
  creationdate,
  sum(bounty) bounty,
  sum(coalesce(accepted+bounty,accepted,bounty)) immune,
  (select min(a) from (
     select sum(rep) a
     union all
     select 200) x) as votes_capped
from
(
select
  p.owneruserid,
  v.creationdate,
  case votetypeid
      when 1 then 15
      end accepted,
  case votetypeid
      when 8 then -bountyamount
      when 9 then bountyamount
      end bounty,
  case votetypeid
      when 2 then 10
      when 3 then -2
      else 0
      end rep
from votes v
inner join posts p on p.id = v.postid
inner join users u on u.id = p.owneruserid
where v.votetypeid in (1,2,3,8,9)
  and p.communityowneddate is null
  and u.reputation >= '20000' -- quick pre-filter
  --and u.id = 22656 -- to check just Jon Skeet
union all
select p2.owneruserid, v.creationdate, null, null, 2
from votes v
inner join posts p1 on p1.id = v.postid
  and v.votetypeid = 1 -- question accepted by asker
inner join posts p2 on p2.id = p1.parentid
inner join users u on u.id = p2.owneruserid
where p2.communityowneddate is null
  and u.reputation >= 20000 -- quick pre-filter
  --and u.id = 22656 -- to check just Jon Skeet
) j
group by owneruserid, creationdate
;

-- incidentally the below also shows the highest one day score, ever
--select top 25 owneruserid as [User Link], *
--from #t order by 7 desc -- to check temp table data.

with cte as (
  select *,
    first_date = creationdate,
    running_total = isnull(total,0),
    bounty_included = isnull(bounty,0)
  from #t
  where rn=1
  union all
  select t.*,
    cte.first_date,
    cte.running_total + isnull(t.total,0),
    cte.bounty_included + isnull(t.bounty,0)
  from cte
  join #t t on t.rn = cte.rn+1 and t.owneruserid = cte.owneruserid
  where cte.running_total < 10000
)
select
       [User Link] = owneruserid,
       [Days Taken] = datediff(d, first_date, creationdate),
       owneruserid,
       [First vote received] = convert(varchar,first_date,102),
       [10k reached] = convert(varchar,creationdate,102),
       [Bounty Included] = bounty_included
from cte
where running_total >= 20000
order by datediff(d, first_date, creationdate)
option (maxrecursion 32767)
;

drop table #t
;

fork query

edited mar 29 14 anonymous

created feb 17 11 cyberkiwi2010