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