3

Please login or register to vote for this query.

(click on this box to dismiss)

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

/*
  Inspiration for cross-site query from:
    Mark Hurd, http://data.stackexchange.com/stackoverflow/query/69700/generate-emailhash-comparison
    Tim Stone, http://data.stackexchange.com/stackoverflow/query/72411
 */
/*
  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. http://dba.stackexchange.com
    ~~  a literal '~'
 */

declare @firstToK as int = ##FirstTo:int?10000##;

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

create table #sitebegin (
    DName NVarchar(100),
    FirstUser DateTime
);

create table #sitestats (
    Id int identity not null,
    Site NVarchar(250),
    DName NVarchar(100),
    UserName NVarchar(50),
    UserLink NVarchar(250),
    DaysToRep int);
    
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'
UNION select 'StackExchange.Meta'

open SITES

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

    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
                           end;
    if @pos > 0 begin
        select @http = @http + substring(@org, @pos + 1, len(@org) - @pos) + '.',
               @org = left(@org, @pos - 1)
    end
    
    select @http = @http + @org + '.com'
    
    set @query = '
           insert into #sitebegin
           select ''' + @sitedispname + ''', min(CreationDate)
           from [' + @sitedbname + ']..Users
    ';
    
    EXEC sp_executesql @query;
    
    select @firstvote = FirstUser
    from #sitebegin
    where DName = @sitedispname

    print 'Processing site ' + @sitedbname + ' -> ' + @http 
        + ' started ' + Convert(NVarchar(max), @firstvote);
        
    -- insert the top X users in to the site stats for 
     
     
    set @query = '
    
declare @minrep as int = ##minrep:int?10000##;

with TopUsers as (
    select Id as TopUser, DisplayName
    from Users
    where Reputation >= @minrep
), RepDays as (
            --Approved suggested edits.
            select TopUser, 
                   Convert(Date, se.ApprovalDate) as tstamp,
                   sum(2) as rep
            from TopUsers inner join 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,
                   sum(
                   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 Posts p on TopUser = p.OwnerUserId
              and p.PostTypeId in (1,2)
            inner join 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 Votes v on TopUser = v.UserId
              and v.VoteTypeId = 8
            group by TopUser, Convert(Date, v.CreationDate)
), RepCumulative as (

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

), CarryOver as (
    select TopUser, min(tstamp) as tstamp
    from RepCumulative
    where CumRep >= @minrep
    group by TopUser
)
insert into #sitestat
select rc.TopUser, rc.tstamp, rc.CumRep
from RepCumulative as rc
   inner join CarryOver co on
       rc.TopUser = co.TopUser
   and rc.tstamp = co.tstamp
order by rc.tstamp  
    
';
 
    fetch next from SITES into @sitedbname
END

close SITES
deallocate SITES
;

select * from #sitebegin

view current version

edited dec 10 14

created dec 10 14 rolfl