Data explorer: Query to match top

I have a client with a bizarre use case that wants TOP to be able to run a contest.

I started with discourse/top_topic.rb at master · discourse/discourse · GitHub 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

This sounds like a pretty good strategy to me as it would be resilient to any future changes in the implementation of /top.

4 Likes