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?
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.
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).
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