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