I think you can get ups by answering these old, unanswered questions

1

Please login or register to vote for this query.

(click on this box to dismiss)

Music Fans Meta

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
);

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

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results