Q&A about the site for music historians, critics, and fans
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## ); INSERT INTO @tagScore 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; SELECT * FROM @tagScore; /* WITH Raw AS ( SELECT Posts.Id AS PostId, Posts.OwnerUserId AS UserId, Posts.ViewCount, Posts.CreationDate, DATEDIFF(s, Posts.CreationDate, @now) AS AgeS FROM PostTags, Posts WHERE PostTags.TagId = @tagId AND PostTags.PostId = Posts.Id AND 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 PostId AS [Post Link], UserId AS [User Link], ViewCount, CreationDate, 86400.0 * ViewCount / AgeS AS ViewsPerDay FROM Raw ORDER BY ViewsPerDay DESC, PostId DESC