Calculating most popular posts with SubText
How do you get the most popular posts? There are three criteria that you want to check:
- Aggregator Views
- Web Views
- Comments
I decided to give each the following ranking:
- Each aggregator view counts as 10 points - a lot of people are subscribe to the feed and so they are more likely to go through all the posts
- Each web view counts counts as 15 points - somebody bothered to go to the site, or arrive via search / link.
- Each comment gets 35 points - somebody took the time to comment on what I said
This means that a post doesn't get too wieghted toward too much comments (which can happen if a discussion is started) but not enough views.
SELECT TOP 15
EntryId,
Title,
WebCount,
AggCount,
CommentsPerPost,
( WebCount * 15 ) + ( AggCount * 10 ) + ( CommentsPerPost * 35 ) weightedScore
FROM subtext_EntryViewCount,
( SELECT COUNT(dbo.subtext_Feedback.Id) CommentsPerPost,
dbo.subtext_Content.Id
FROM dbo.subtext_Feedback
RIGHT JOIN dbo.subtext_Content ON dbo.subtext_Content.Id = dbo.subtext_Feedback.EntryId
WHERE FeedbackType = 1
GROUP BY dbo.subtext_Content.Id
) Comments,
Subtext_Content
WHERE Comments.Id = EntryId
AND Subtext_Content.Id = EntryId
ORDER BY ( WebCount * 15 ) + ( AggCount * 10 ) + ( CommentsPerPost * 35 ) DESC