Number and % of (new) Hot Network Questions during a certain period

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

CREATE TABLE #results (site NVARCHAR(250), HNQs INT, questions INT);                       
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + IIF(LEN(@sql) > 0, 'UNION ALL', 'INSERT INTO #results') + N'
SELECT ''' + name + ''',
  (SELECT COUNT(DISTINCT(PostId))
  -- DISTINCT because of https://meta.stackexchange.com/q/327889/295232
  FROM ' + QUOTENAME(name) + '.dbo.PostHistory
  WHERE PostHistoryTypeId = 52 -- became Hot Network Question
    AND CreationDate BETWEEN ''' + ##fromDate:string## + '''
                         AND ''' + ##tillDate:string## + '''),
  (SELECT COUNT(*)
  FROM ' + QUOTENAME(name) + '.dbo.PostsWithDeleted
  WHERE PostTypeId = 1 -- questions
    AND CreationDate BETWEEN ''' + ##fromDate:string## + '''
                         AND ''' + ##tillDate:string## + ''')                       
'
FROM sys.databases
WHERE database_id > 5
  AND name NOT LIKE '%.Meta';
EXEC (@sql);

-- Site details, cf. https://meta.stackexchange.com/a/359795/295232
CREATE TABLE #Sites (URL NVARCHAR(100), SiteName NVARCHAR(100), DatabaseName NVARCHAR(50));
-- fetch Markdown for post containing table with site details
DECLARE @md NVARCHAR(MAX);
SELECT TOP 1 @md = Text
  FROM [StackExchange.Meta].dbo.PostHistory
  WHERE PostId = 359796 AND PostHistoryTypeId = 5
  ORDER BY CreationDate DESC;
-- split Markdown on pipe symbol
CREATE TABLE #TableEntries (ID INT IDENTITY(1, 1), Value NVARCHAR(100));
INSERT INTO #TableEntries (Value)
SELECT LEFT(TRIM(VALUE), 100) FROM STRING_SPLIT(@md, '|')
  WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0;
-- 'pivot' - we have to discard the introductory text, headers and horizontal lines
INSERT INTO #Sites
SELECT t0.Value, t1.Value, t2.Value
  FROM #TableEntries AS t0
  INNER JOIN #TableEntries AS t1
    ON t0.Id + 1 = t1.Id
  INNER JOIN #TableEntries AS t2
    ON t0.Id + 2 = t2.Id
  WHERE t0.Id >= 8 -- that's where the real content starts
    AND t0.Id % 3 = 2;

SELECT RANK() OVER (ORDER BY ROUND(100.0 * HNQs / Questions, 2) DESC) AS 'rank',
  SiteName AS 'site',
  ROUND(100.0 * HNQs / Questions, 2) AS '% of Qs that become HNQ',
  HNQs AS '# of HNQs',
  Questions AS '# of questions'
  FROM #Results AS r
  INNER JOIN #Sites AS s
    ON r.Site = s.DatabaseName
  ORDER BY 3 DESC;

DROP TABLE #results;

Enter Parameters

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