First to Rep users for each site


Please login or register to vote for this query.

(click on this box to dismiss)

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

  Inspiration for cross-site query from:
    Mark Hurd,
    Tim Stone,
  We build a dynamic query as a prelude, then @Template for each site
  with @Separator in between, and finally a postlude.
  First, the template, which may contain the following escape strings:
    ~n  the "pretty" site name (or close enough), e.g. 'Dba'
    ~u  the URL of the site, e.g.
    ~~  a literal '~'

declare @overflowOnly as NVarchar(10) = ltrim( rtrim (lower( ##StarOverflows:string?no## ) ) );
declare @like as NVarchar(50);
declare @firstToKNUM as int = ##FirstTo:int?10000##;
declare @firstToK as NVarchar(10);
declare @topxNUM as int = ##TopX:int?3##;
declare @topx as NVarchar(10);

select @topx = Convert(NVarchar(10), @topxNUM),
       @firstToK = Convert(NVarchar(10), @firstToKNUM);

declare @sitedbname as NVarchar(100),
        @sitedispname as NVarchar(100),
        @query as nvarchar(MAX),
        @firstvote as DateTime;

create table #sitestats (
    Site NVarchar(250),
    DName NVarchar(100),
    UserName NVarchar(50),
    UserId int,
    FirstDate Date,
    MadeIt Date,
    Reputation int);
select @overflowOnly = case when @overflowOnly = 'yes' or @overflowOnly = '1'
                    then 'yes'
                    else 'no'
declare SITES cursor for

select Substring(Name, 0, Len(Name) - 4 )
from sys.Databases
where Name like '%.Meta'
  --and Name = 'StackExchange.Codereview.Meta'
  and not Name = 'StackExchange.Meta'
  --and not Name like '%Overflow%'
-- UNION select 'StackExchange.Meta'

open SITES

fetch next from SITES into @sitedbname
while @@FETCH_STATUS = 0

    declare @org as NVarchar(100), @http as NVarchar(100), @pos as int;
    select @org = lower(@sitedbname),
           @http = 'http://',
           @pos = CharIndex('.', @sitedbname),
           @sitedispname = case when @sitedbname like 'StackExchange.%'
                           then substring(@sitedbname, 15, 100)
                           else @sitedbname
    if @pos > 0 begin
        select @http = @http + substring(@org, @pos + 1, len(@org) - @pos) + '.',
               @org = left(@org, @pos - 1)
    select @http = @http + @org + '.com'
    print 'Processing site ' + @sitedbname + ' -> ' + @http;
    -- insert the top X users in to the site stats for 
    set @query = '
with FirstDay as (

    select Convert(Date, min(CreationDate)) as FirstDate,
           ''' + @http + ''' as Site,
           ''' + @sitedispname + ''' as DName
    from [' + @sitedbname + ']..Users

), TopUsers as (

    select Id as TopUser, DisplayName
    from [' + @sitedbname + ']..Users
    where Reputation >= ' + @FirstToK + '
), RepDays as (
            --Approved suggested edits.
            select TopUser, 
                   Convert(Date, se.ApprovalDate) as tstamp,
                   sum(2) as rep
            from TopUsers inner join [' + @sitedbname + ']..SuggestedEdits se on TopUser = se.OwnerUserId
              and se.ApprovalDate is not null
            group by TopUser, Convert(Date, se.ApprovalDate)
         UNION ALL
            -- Up and Down votes on Qs and As
            select TopUser,
                   Convert(Date, v.CreationDate) as tstamp,
                   case when v.VoteTypeId = 1 then 15
                        when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer
                        when v.VoteTypeId = 3 then -2
                        when v.VoteTypeId = 9 then IsNull(v.BountyAmount, 0)
                        else 0
                   end) as rep
            from TopUsers
            inner join [' + @sitedbname + ']..Posts p on TopUser = p.OwnerUserId
              and p.PostTypeId in (1,2)
            inner join [' + @sitedbname + ']..Votes v on p.Id = v.PostId
              and v.VoteTypeId in (1,2,3,9)
            group by TopUser, Convert(Date, v.CreationDate)
         UNION ALL
            -- Bounties that were offered....
            select TopUser,
                   Convert(Date, v.CreationDate) as tstamp,
                   sum(-1 * v.BountyAmount) as rep
            from TopUsers inner join [' + @sitedbname + ']..Votes v on TopUser = v.UserId
              and v.VoteTypeId = 8
            group by TopUser, Convert(Date, v.CreationDate)

), RepCumulative as (

    select TopUser,
           sum (sum(rep)) over (partition by TopUser order by tstamp) as CumRep
    from RepDays
    group by TopUser, tstamp

), CarryOver as (
    select TopUser, min(tstamp) as tstamp
    from RepCumulative
    where CumRep >= ' + @FirstToK + '
    group by TopUser
insert into #sitestats
select Top ' + @topx + '
       Site as Site,
       DName as DName,
       u.DisplayName as UserName,
       u.Id as UserId,
       FirstDate as FirstDate,
       rc.tstamp as MadeIt,
       rc.CumRep as Reputation
from RepCumulative as rc
   inner join CarryOver co on
       rc.TopUser = co.TopUser
   and rc.tstamp = co.tstamp
   inner join [' + @sitedbname + ']..Users u on
       rc.TopUser = u.Id
   inner join FirstDay on 1 = 1
order by rc.tstamp


    if @overflowOnly = 'yes' and @sitedbname = 'StackOverflow'
    or @overflowOnly <> 'yes' and @sitedbname <> 'StackOverflow'
        EXEC sp_executesql @query
        print @query
    fetch next from SITES into @sitedbname

close SITES
deallocate SITES

select Rank() over (Order by DateDiff(dd, FirstDate, MadeIt)) as Rank,
       Site + '/users/' + Convert(NVarchar(10), UserId) + '|' + Username as [User Name],
       UserName as [For Sort],
       DName as [SE Site],
       Reputation as Reputation,
       DateDiff(dd, FirstDate, MadeIt) as [Days],
       FirstDate as [SiteStart],
       MadeIt as [Made It]
from #sitestats
order by DateDiff(dd, FirstDate, MadeIt)

Enter Parameters

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