0

Please login or register to vote for this query.

(click on this box to dismiss)

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

CREATE TABLE #results (site NVARCHAR(250),
                       hourofweek INT, hotquestions INT, allquestions INT);

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + IIF(LEN(@sql) > 1, 'UNION ALL', 'INSERT INTO #results') + N'
-- Sunday = 1, Saturday = 7
select ''' + name + ''',
  (datepart(weekday, p.creationdate) - 1) * 24 + datepart(hour, p.creationdate),
  count(h.id), count(*)
  from ' + quotename(name) + '.dbo.posts as p
  left outer join ' + quotename(name) + '.dbo.posthistory as h
    on h.postid = p.id and h.posthistorytypeid = 52
  where p.creationdate >= ''2019-03-01'' -- start date for HNQ entry records
  group by datepart(weekday, p.creationdate), datepart(hour, p.creationdate)
'
FROM sys.databases
WHERE database_id > 5
  AND name NOT LIKE '%.Meta';
  
EXEC(@sql);

SELECT hourofweek AS 'Hour of week',
  SUM(hotquestions) * 100 / SUM(allquestions) AS '%'
  FROM #results
  GROUP BY hourofweek
  ORDER BY hourofweek;
     
DROP TABLE #results;

view current version

edited mar 14 21

created mar 14 21 Glorfindel