Q&A for Ubuntu users and developers
CREATE TABLE #output ( DbName VARCHAR(MAX), ElectionId INT, PostId INT, CreationDate DATETIME, UserName VARCHAR(MAX), UserId INT, UserAccountId INT ); DECLARE @query VARCHAR(MAX); DECLARE dbcursor CURSOR FOR SELECT name FROM sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange'); DECLARE @DbName VARCHAR(MAX); OPEN dbcursor; FETCH NEXT FROM dbcursor INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @query = ' WITH Raw AS ( SELECT LAG(CreationDate) OVER (ORDER BY CreationDate) AS PrevCrDate, Id, OwnerUserId AS UserId, CreationDate FROM [' + @dbname + '].dbo.PostsWithDeleted WHERE PostTypeId = 6 ) SELECT ''' + @dbname + ''' AS DbName, SUM( CASE WHEN PrevCrDate IS NULL OR DATEDIFF(day, Raw.PrevCrDate, Raw.CreationDate) > 30 THEN 1 ELSE 0 END ) OVER ( ORDER BY Raw.CreationDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS ElectionId, Raw.Id AS PostId, Raw.CreationDate, Users.DisplayName AS UserName, Raw.UserId, Users.AccountId AS UserAccountId FROM Raw LEFT JOIN [' + @dbname + '].dbo.Users ON Raw.UserId = Users.Id '; INSERT INTO #output EXEC (@query); FETCH NEXT FROM dbcursor INTO @DbName; END CLOSE dbcursor; SELECT *, COUNT(*) OVER (PARTITION BY UserAccountId) AS UserNominationCount FROM #output ORDER BY CreationDate;