Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 18 | Comments: 87

filter by tags archive

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


Comments

Ayende Rahien

Sorry, that counts only those that have at least one comment.

Shiva

This is great ! I was looking to do something like this, just like Community Server has "Most Popular" sections...

Do you think it could be made even more configurable, by passing the points for each type from web.config or somewhere, so that individual bloggers can configure their "most popular" post without recompiling the SP ?

-Shiva

Ayende Rahien

You could pass parameters for the different types, so that wouldn't be a problem

Steve Harman

sniff, sniff

I think I smell a the start of a really cool Plug-in for Subtext v2.0!

This query, and some of the ones from your previous "Blog Stats: SQL" post would be a solid basis around with to build a plugin/skin control for showing off some of the less obvious (but very interesting) stats that your blog has to offer.

verns blog

My 15 Most Popular Posts, via Steve, via Ayende

Community Blogs

Technorati recently released their latest State of The Blogosphere report (renamed to something about

Eduardo Miranda

I just migrated to SubText yesterday and a I used your query to learn a little bit about the SubText database.

Because my blog is not as popular as yours, I found a small "bug" (maybe designed that way) in the query. Posts that don't have comments (as I said, I'm not that popular) doesn't show in the results.

I did a small change in the query to fix it:

WHERE FeedbackType = 1 OR FeedbackType IS NULL

Now it's working for me

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. Buffer allocation strategies: A possible solution - about one day from now
  2. Buffer allocation strategies: Explaining the solution - 3 days from now
  3. Buffer allocation strategies: Bad usage patterns - 4 days from now
  4. The useless text book algorithms - 5 days from now
  5. Find the bug: The concurrent memory buster - 6 days from now

There are posts all the way to Sep 11, 2015

RECENT SERIES

  1. Find the bug (5):
    20 Apr 2011 - Why do I get a Null Reference Exception?
  2. Production postmortem (10):
    03 Sep 2015 - The industry at large
  3. What is new in RavenDB 3.5 (7):
    12 Aug 2015 - Monitoring support
  4. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats