Calculating most popular posts with SubText
time to read 7 min | 1263 words

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

Tweet Share Share 9 comments
Tags: