I have a client with a bizarre use case that wants TOP to be able to run a contest.
I started with https://github.com/discourse/discourse/blob/master/app/models/top_topic.rb#L143-L192 and came up with the below, but it’s not matching top. Is there something obvious I’m missing? My fall-back notion is to just pull the json files each day with a cron job and write a json-to-csv script for the data they want.
-- [params]
-- date :start_date = '2019-05-01'
-- date :stop_date = '2019-05-30'
-- int :log_views_multiplier = 2
-- int :least_likes_per_post_multiplier = 3
-- int :num_topics = 100
WITH top_topics AS(
SELECT t.like_count likes_count,
p.like_count score,
t.id topic_id,
t.posts_count posts_count,
p.like_count op_likes_count,
t.title topic_name,
t.views views_count,
p.user_id user_id,
t.category_id category_id
FROM topics t
JOIN posts p ON p.topic_id = t.id AND p.post_number = 1
WHERE t.created_at > to_date(:start_date, 'YYYY-MM-DD')
AND t.created_at < to_date(:stop_date, 'YYYY-MM-DD')
),
top AS (
SELECT log(GREATEST(views_count, 1)) * :log_views_multiplier +
op_likes_count * 0.5 +
CASE WHEN top_topics.likes_count > 0 AND top_topics.posts_count > 0
THEN
LEAST(top_topics.likes_count / top_topics.posts_count, :least_likes_per_post_multiplier )
ELSE 0
END +
CASE WHEN topics.posts_count < 10 THEN
0 - ((10 - topics.posts_count) / 20) * op_likes_count
ELSE
10
END +
log(GREATEST(top_topics.posts_count, 1))
AS score,
topic_id
FROM top_topics
LEFT JOIN topics ON topics.id = top_topics.topic_id AND
topics.deleted_at IS NULL
)
select topic_id,topic_name, t.user_id, u.username, likes_count, category_id
from top_topics t
left join users u
on t.user_id = u.id
order by likes_count desc
limit :num_topics