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

**URL:** <https://meta.discourse.org/t/should-top-include-topics-older-topics-with-new-posts-in-the-selected-period/76670>\
**Category:** Feature\
**Created:** [2017年十二月23日 19:02 UTC](https://meta.discourse.org/t/should-top-include-topics-older-topics-with-new-posts-in-the-selected-period/76670 "2017-12-23T19:02:52Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![mcwumbly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mcwumbly/32/103861_2.png) [@mcwumbly](https://meta.discourse.org/u/mcwumbly)\
**Post date:** [2017年十二月28日 14:52 UTC](https://meta.discourse.org/t/should-top-include-topics-older-topics-with-new-posts-in-the-selected-period/76670/4 "2017-12-28T14:52:24Z")

</div>

OK, here’s a stab at a [data explorer](https://meta.discourse.org/t/32566?silent=true) 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.

```plaintext
-- [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

```

---

_[View the full topic](https://meta.discourse.org/t/should-top-include-topics-older-topics-with-new-posts-in-the-selected-period/76670)._