3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for ebook publishers and readers

/*
  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 @overflowOnly as NVarchar(10) = ltrim( rtrim (lower( ##SiteOrAllMinusSO:string?all## ) ) );
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);
    
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
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'
    
    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 
                   sum(
                   case when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer
                        when v.VoteTypeId = 3 then -2
                        else 0
                   end)
                   > 200 then 200 else
                   sum(
                   case when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer
                        when v.VoteTypeId = 3 then -2
                        else 0
                   end)
                   end
                   +
                   sum(
                   case when v.VoteTypeId = 1 then 15
                        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,
           tstamp,
           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 = 'all' and @sitedbname <> 'StackOverflow')
    or lower(@sitedbname) like '%' + lower(@overflowOnly) + '%'
        EXEC sp_executesql @query
    else
        print '"' + @overflowOnly + '" -> 
              ' + @query
 
    fetch next from SITES into @sitedbname
END

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)

fork query

edited dec 11 14

created dec 10 14 rolfl