0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Tridion developers and administrators

DECLARE @UserId INT = ##UserId##
CREATE TABLE #tags (TagId INT, [Count] INT)

INSERT #tags
SELECT TOP 20
  TagId,
  COUNT(*) AS UpVotes
FROM Tags
INNER JOIN PostTags ON PostTags.TagId = Tags.Id
INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
INNER JOIN Votes ON Votes.PostId = Posts.Id AND VoteTypeId = 2
WHERE Posts.OwnerUserId = @UserId
GROUP BY TagId
ORDER BY UpVotes DESC

CREATE TABLE #unanswered (Id INT PRIMARY KEY)

INSERT #unanswered
SELECT DISTINCT q.Id
FROM Posts q
INNER JOIN PostTags pt ON pt.PostId = q.Id
INNER JOIN #tags t ON t.TagId = pt.TagId
WHERE (SELECT COUNT(*) FROM Posts a WHERE a.ParentId = q.Id AND a.Score > 0) = 0
AND q.CommunityOwnedDate IS NULL
AND q.ClosedDate IS NULL
AND q.AcceptedAnswerId IS NULL
AND (NOT OwnerUserId = @UserId)

SELECT TOP 2000 u.Id AS [Post Link],
(SUM(t.[Count]) / 10.0 + us.Reputation / 200.0 + p.Score * 100) AS Weight
FROM #unanswered u
JOIN Posts p ON u.Id = p.Id
JOIN PostTags pt ON pt.PostId = u.Id
JOIN #tags t ON t.TagId = pt.TagId
JOIN Users us ON us.Id = p.OwnerUserId
GROUP BY u.Id, us.Reputation, p.Score
ORDER BY Weight DESC

fork query

created nov 22 CrSb0001