0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for power users of web applications

-- Retrieve the most interesting answered physics questions based on combined weight
-- Weight calculation includes question score, asker reputation, and answer score

DECLARE @TopTags int = 20
DECLARE @TopPosts int = 2000

-- Temporary table for top tags based on upvotes
CREATE TABLE #tags (TagId INT, [Count] INT)

INSERT INTO #tags 
SELECT TOP (@TopTags)
    TagId,
    COUNT(*) AS UpVotes
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts ON Posts.Id = PostTags.PostId
GROUP BY TagId
ORDER BY UpVotes DESC

-- Temporary table for interesting answered questions
CREATE TABLE #answered (Id INT PRIMARY KEY)

INSERT INTO #answered 
SELECT q.Id  
FROM Posts q
WHERE (SELECT COUNT(*) FROM Posts a WHERE a.ParentId = q.Id AND a.Score > 0) > 0
  AND q.Score > 0
  AND q.PostTypeId = 1 -- Ensures only questions are selected
  AND q.AcceptedAnswerId IS NOT NULL

-- Select top interesting questions with high weight based on score and reputation
SELECT TOP (@TopPosts) ans.Id AS [Post Link],
  (q.Score * 1.5 + us.Reputation / 100.0 + ISNULL(MAX(a.Score), 0) * 2) AS Weight
FROM #answered ans
JOIN Posts q ON ans.Id = q.Id
JOIN PostTags pt ON pt.PostId = ans.Id
JOIN #tags t ON t.TagId = pt.TagId
JOIN Users us ON us.Id = q.OwnerUserId
LEFT JOIN Posts a ON a.ParentId = ans.Id AND a.PostTypeId = 2 -- Join to answers only
GROUP BY ans.Id, us.Reputation, q.Score
ORDER BY Weight DESC

fork query

created nov 9 anonymous