Late Hot Network Questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Philosophy Meta

Q&A about the site for those interested in the study of the fundamental nature of knowledge, reality, and existence

-- result table, don't rename and keep the site column
CREATE TABLE #results (site NVARCHAR(250),
                       id INT,
                       title NVARCHAR(250),
                       age 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 ALL'
     , 'INSERT INTO #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
SELECT ''' + name + ''', p.Id, p.Title,
  DATEDIFF(SECOND, p.CreationDate, h.CreationDate)
  FROM ' + quotename(name) + '.dbo.PostHistory AS h
  INNER JOIN ' + quotename(name) + '.dbo.Posts AS p
    ON h.PostId = p.Id
  WHERE h.PostHistoryTypeId = 52
    AND h.CreationDate >= ''2019-03-01'' -- when this feature was introduced, all then-hot questions got a post history entry as well
'
FROM sys.databases
WHERE database_id > 5
  AND (name NOT LIKE '%.Meta' OR name = 'StackExchange.Meta')

-- execute it
EXEC (@sql)

SELECT site,
  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'
          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'
            )
          )
        )
      )
      )) + '/q/' + CAST(id AS nvarchar) + '|' + title,
      age / 3600.0 AS 'Age in hours when entering HNQ'
  FROM #results
  ORDER BY age DESC
     
DROP TABLE #results

Enter Parameters

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