Q&A about the site for artists and crafters
DECLARE @now DATETIME = ( SELECT CreationDate FROM Posts WHERE Id = (SELECT MAX(Id) FROM Posts) ); DECLARE @tagScore TABLE ( TagId INT NOT NULL, Score INT NOT NULL, PRIMARY KEY(TagId) ); DECLARE @yourUserId INT = ( SELECT Id FROM Users WHERE AccountId = ##AccountId:int## ); WITH TagScores AS ( SELECT PostTags.TagId, SUM(A.Score) AS Score FROM Posts A, PostTags WHERE A.OwnerUserId = @yourUserId AND PostTags.PostId = A.ParentId GROUP BY PostTags.TagId HAVING SUM(A.Score) > 0 ), Qs AS ( SELECT Posts.Id AS PostId, Posts.OwnerUserId AS UserId, Posts.ViewCount, Posts.CreationDate, DATEDIFF(s, Posts.CreationDate, @now) AS AgeS FROM Posts WHERE Posts.OwnerUserId > 0 AND Posts.AnswerCount = 0 AND Posts.ClosedDate IS NULL AND Posts.OwnerUserId IN ( SELECT OwnerUserId FROM Posts WHERE PostTypeId = 2 AND CreationDate > DATEADD(d, -##AnswerDays:int?30##, @now) ) ) SELECT * FROM Qs; /*, Raw AS ( SELECT Qs.PostId, Qs.UserId, Qs.ViewCount, Qs.CreationDate, 86400.0 * MIN(Qs.ViewCount) / MIN(Qs.AgeS) AS ViewsPerDay, SUM(TagScores.Score) AS SumScore, FROM TagScores, Qs, PostTags WHERE TagScores.TagId = PostTags.TagId AND Qs.Id = PostTags.PostId GROUP BY Qs.Id ) SELECT TOP 1000 PostId AS [Post Link], UserId AS [User Link], ViewCount, CreationDate, ViewsPerDay FROM Raw ORDER BY ViewsPerDay DESC, PostId DESC