Should "Top" include topics older topics with new posts in the selected period?


(Dave McClure) #1

One of the best features of Discourse, vs. something like Slack, is that discussions can unfold over a long period of time. I love it when I see a thread that has laid dormant for 3 months revived with new thoughts and ideas while retaining the context of the earlier discussion.

In this new weekly newsletter I’m experimenting with, I’d love to be able to see a list of the top topics for the past week that includes older topics as long as they’ve had activity in the past week. Sometimes the best discussions in the past week were discussions taking place on older topics.

What do others think? Should /top include older topics with activity within the date range? Or continue to only list topics created within the selected date range?


A curated weekly newsletter for your community
(Erlend Sogge Heggen) #2

This is interesting, but we’d have to come up with a clever way to exclude bumped topics.

The announcement of Discourse’s Fourth Birthday is currently ranked at the top for the past year. It was bumped in November. So the logic can’t be quite as simple as “include older topics with any new activity within the date range” because then this would be at the top of the past quarter as well, which is not desirable.


(Dave McClure) #3

Yeah, I was thinking it be a ranking of actual activity over the selected timeframe. So one post would probably rank pretty low. (Admittedly, the idea is not fully baked though…I’ll study the current algorithm a little more closely and reply again if I’m able to mock something up that better explains what I mean).


(Dave McClure) #4

OK, here’s a stab at a data explorer query that gets some raw data for calculating scores based on activity within a time period.

  • posts_created = posts created in a given time period within each topic
  • posts_viewed = views of those posts_created within each topic
  • posts_liked = likes of those posts_created within each topic

Not sure then what the right weighting of the above would be to come up with a “score”, but this at least gives folks something to look at and compare to the /top list to compare.

-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 28

with
t as (
  select 
    current_date::timestamp - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
    current_date::timestamp - (:from_days_ago * (INTERVAL '1 days')) as end
),
period_posts as (
  select id, topic_id, post_number
  from posts, t
  where created_at > t.start
  and created_at < t.end
  and user_id > 0 --exclude system user (eg. auto-close posts)
),
topic_post_counts as (
  select topic_id,
      count(1) as posts_created
  from period_posts
  group by topic_id
),
period_likes as (
  select post_id,
     count(1) as likes
  from post_actions
  where post_action_type_id = 3
  and post_id
  in (select id from period_posts)
  group by post_id
),
topic_like_counts as (
  select topic_id,
    count(1) as likes
  from period_posts pp
  left join period_likes pl
  on pp.id = pl.post_id
  group by topic_id
),
topic_view_counts as (
  select topic_id, 
      count(1) as views
  from post_timings
  where (topic_id, post_number) in (select topic_id, post_number from period_posts)
  group by topic_id
)
select tpc.topic_id, 
  t.created_at as created_at,
  tpc.posts_created as posts_created,
  tvc.views as posts_viewed,
  tlc.likes as posts_liked
  from topic_post_counts tpc
  left join topic_view_counts tvc
  on tpc.topic_id = tvc.topic_id
  left join topic_like_counts tlc
  on tpc.topic_id = tlc.topic_id
  left join topics t
  on tpc.topic_id = t.id
  order by posts_viewed desc

What's up with these 1ms views?
(Jeff Atwood) #5

Hmm maybe but I feel this opens just as many new cans of worms as it “resolves”…

@neil bearing in mind what @erlend_sh said above, any thoughts?


(Neil Lalonde) #6

I’m not following this… So it’s not based on bumped topics, but on something else?


(Jeff Atwood) #7

In a digest for a specific date range, any posts in that date range make a topic eligible for inclusion, even if it was created years ago.