1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for pro webmasters

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;

/*
CREATE TABLE #Q (
  PostId INT NOT NULL,
  UserId INT NOT NULL,
  ViewCount INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  AgeS INT NOT NULL,
  PRIMARY KEY(PostId)
);

INSERT INTO #Q
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 COUNT(*) FROM #Q;

/*
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

view current version

edited nov 25

created nov 25 peterh - PLEASE DELETE THIS ACCOUNT