1

Please login or register to vote for this query.

(click on this box to dismiss)

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 @Qs TABLE (
  PostId INT NOT NULL,
  UserId INT NOT NULL,
  ViewCount INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  AgeS INT NOT NULL,
  PRIMARY KEY(PostId)
);

DECLARE @yourUserId INT = (
  SELECT Id FROM Users WHERE AccountId = ##AccountId:int##
);

INSERT INTO @Qs
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 TOP 100 * FROM @Qs;
/*
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
),
Raw AS (
  SELECT
    Qs.PostId,
    MIN(Qs.UserId) AS UserId,
    MIN(Qs.ViewCount) AS ViewCount,
    MIN(Qs.CreationDate) AS 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.PostId = PostTags.PostId
  GROUP BY Qs.PostId
  HAVING SUM(TagScores.Score) > 0
)
SELECT TOP 100
  PostId AS [Post Link],
  UserId AS [User Link],
  ViewCount,
  CreationDate,
  ViewsPerDay
FROM Raw
ORDER BY ViewsPerDay * SumScore DESC, PostId DESC

view current version

edited nov 25

created nov 25 peterh - PLEASE DELETE THIS ACCOUNT