Sites where the top askers are similar to the top answerers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250)
                       , users int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + ''', COUNT(*) FROM
(SELECT TOP ' + CAST(##Limit:int?100## AS NVARCHAR) + ' OwnerUserId
  FROM ' + quotename(name) + '.dbo.Posts
  WHERE PostTypeId = 1 -- Questions
  GROUP BY OwnerUserId
  ORDER BY COUNT(*) DESC) AS Q
INNER JOIN
(SELECT TOP ' + CAST(##Limit:int?100## AS NVARCHAR) + ' OwnerUserId
  FROM ' + quotename(name) + '.dbo.Posts
  WHERE PostTypeId = 2 -- Answers
  GROUP BY OwnerUserId
  ORDER BY COUNT(*) DESC) AS A
ON Q.OwnerUserId = A.OwnerUserId
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

-- execute it
exec (@sql)

select
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Health' THEN 'medicalsciences'
          WHEN 'Health.Meta' THEN 'medicalsciences.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      )) + '|' + site,
      ROUND(users * 100.0 / ##Limit:int?100##, 2)
  FROM #results
  ORDER BY users DESC

drop table #results

Enter Parameters

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