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

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
4 Likes