Q&A for Drupal developers and administrators
DECLARE @userId INT = ( SELECT Id FROM Users WHERE AccountId = ##NetworkAccountId:int## ); DECLARE @now DATETIME = ( SELECT MAX(CreationDate) FROM PostsWithDeleted ); CREATE TABLE #FirstQuestionsRaw ( Id INT NOT NULL PRIMARY KEY, CreationDate DATETIME NOT NULL, ViewCount INT NOT NULL ); CREATE TABLE #FirstQuestions ( Id INT NOT NULL PRIMARY KEY, CreationDate DATETIME NOT NULL, ViewCount INT NOT NULL, TagWeight INT NOT NULL ); WITH FirstQuestionsRaw AS ( SELECT RANK() OVER (PARTITION BY OwnerUserId ORDER BY Id) AS Rank, Id FROM Posts WHERE PostTypeId = 1 AND OwnerUserId > 0 ) INSERT INTO #FirstQuestionsRaw SELECT Posts.Id, Posts.CreationDate, Posts.ViewCount FROM FirstQuestionsRaw, Posts WHERE FirstQuestionsRaw.Id = Posts.Id AND FirstQuestionsRaw.Rank <= ##FirstNQuestions:int?3## AND Posts.ClosedDate IS NULL AND Posts.CommunityOwnedDate IS NULL AND Posts.AnswerCount = 0 AND Posts.CreationDate < DATEADD(d, -7, @now); WITH YourTagScores AS ( SELECT PostTags.TagId, SUM(Score) AS Score FROM Posts, PostTags WHERE Posts.OwnerUserId = @userId AND PostTags.PostId = Posts.ParentId GROUP BY PostTags.TagId HAVING SUM(Score) > 0 ) INSERT INTO #FirstQuestions SELECT #FirstQuestionsRaw.Id, MIN(#FirstQuestionsRaw.CreationDate) AS CreationDate, MIN(#FirstQuestionsRaw.ViewCount) AS ViewCount, SUM(YourTagScores.Score) AS TagWeight FROM #FirstQuestionsRaw, PostTags, YourTagScores WHERE #FirstQuestionsRaw.Id = PostTags.PostId AND PostTags.TagId = YourTagScores.TagId GROUP BY #FirstQuestionsRaw.Id; SELECT TOP 100 Id AS [Post Link], CreationDate, ViewCount, --MIN(#FirstQuestions.Tags) AS Tags, 86400.0 * ViewCount / DATEDIFF(s, CreationDate, @now) AS ViewsPerDay, 86400.0 * ViewCount / DATEDIFF(s, CreationDate, @now) * TagWeight AS Goodness FROM #FirstQuestions ORDER BY Goodness DESC, Id