1

Please login or register to vote for this query.

(click on this box to dismiss)

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

view current version

edited nov 25

created nov 25 peterh - PLEASE DELETE THIS ACCOUNT