Q&A for scientists using computers to solve scientific problems
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 (CASE WHEN Questions = 0 THEN 0 ELSE ROUND(100.0 * HNQs / Questions, 2) END) DESC) AS 'rank', SiteName AS 'site', CASE WHEN Questions = 0 THEN 0 ELSE ROUND(100.0 * HNQs / Questions, 2) END 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;