Average time to first answer, a networkwide comparison

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- result table, don't rename and keep the site column
CREATE TABLE #results (site nvarchar(250),
                       average float);

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 ALL'
     , 'INSERT INTO #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
SELECT ''' + name + ''',
  AVG(DATEDIFF(SECOND, q.CreationDate, a.CreationDate) / 3600.0)
  FROM ' + quotename(name) + '.dbo.Posts AS q
  INNER JOIN ' + quotename(name) + '.dbo.Posts AS a
    ON a.ParentId = q.Id
  LEFT OUTER JOIN ' + quotename(name) + '.dbo.Posts AS a2 -- earlier answer, should not exist
    ON a.ParentId = a2.ParentId
   AND a.CreationDate > a2.CreationDate
  WHERE a2.Id IS NULL
    AND a.CreationDate >= q.CreationDate -- negative values happen after question merges
    AND q.CreationDate BETWEEN ''' + ##dateFrom:string?2018-01-01## + '''
                           AND ''' + ##dateTill:string?2018-07-01## + '''
'
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 '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'
          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,
      average AS 'Time (hours)'
  FROM #results
  WHERE average IS NOT NULL
  ORDER BY average
     
DROP TABLE #results

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results