s1

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Select all valid databases.
select dbid, name
into #dbs
from master..sysdatabases
where name not in ('Data.StackExchange', 'master', 'model', 'msdb', 'tempdb')
  and right(name, 5) <> '.Meta'
  or name = 'StackExchange.Meta'

-- Loop through databases.
create table #sites (
  db varchar(40), SiteAge int,  QuestionCount int, AnswerCount int,
  QUpVoteCount int, QDownVoteCount int, AUpVoteCount int, ADownVoteCount int
)
declare @id int, @db varchar(40), @sql nvarchar(max)
while (select count(*) from #dbs) > 0
begin
  select top 1 @id = dbid, @db = name from #dbs


  -- Fetch each post in database by user.
  set @sql = '
    select p.PostTypeId, v.VoteTypeId, count(v.VoteTypeId) as cnt
    into #tmp
    from [' + @db + ']..[Posts] p
    inner join [' + @db + ']..[Votes] v on p.Id = v.PostId
    group by p.PostTypeId, v.VoteTypeId having p.PostTypeId in (1, 2) and v.VoteTypeId in (2, 3)
    
    insert into #sites
    select
      ''' + @db + ''' as db,
      datediff(month, (select min(CreationDate) from [' + @db + ']..[Posts]), getdate()) as SiteAge,
      (select count(*) from [' + @db + ']..[Posts] where PostTypeId=1) as QuestionCount,
      (select count(*) from [' + @db + ']..[Posts] where PostTypeId=2) as AnswerCount,
      (select cnt from #tmp where PostTypeId=1 and VoteTypeId=2) as QUpVoteCount,
      (select cnt from #tmp where PostTypeId=1 and VoteTypeId=3) as QDownVoteCount,
      (select cnt from #tmp where PostTypeId=2 and VoteTypeId=2) as AUpVoteCount,
      (select cnt from #tmp where PostTypeId=2 and VoteTypeId=3) as ADownVoteCount
  '
  exec(@sql)
  delete from #dbs where dbid = @id
end

-- Print out list of links.
select 
  db, SiteAge, 
  QuestionCount + AnswerCount as Posts,
  cast(QUpVoteCount as float)/cast(AnswerCount as float) as UpVotesPerQuestion,
  cast(AUpVoteCount as float)/cast(QuestionCount as float) as UpVotesPerAnswer,
  cast(QDownVoteCount as float)/cast(AnswerCount as float) as DownVotesPerQuestion,
  cast(ADownVoteCount as float)/cast(QuestionCount as float) as DownVotesPerAnswer
from #sites
order by UpVotesPerQuestion

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results