Calculating "Top" topics in Discourse

Documentation Using Discourse
,
1

:bookmark: This is a reference guide explaining how topics filtered by “Top” are determined to be “Top Topics” in Discourse.

:person_raising_hand: Required user level: All users

Discourse sites have a feature that allows users to sort topics by “Top”. This guide explains how these “Top” topics are calculated and displayed.

Summary

  • All “Top” topics are assigned a “Top Score”
  • The score is based on likes, replies, and views within a selected time period
  • Topics with the highest “Top Score” appear at the top of the list

Top Score calculation

The “Top Score” for a topic is calculated using the following factors:

  1. Number of views
  2. Likes on the first post
  3. Likes on subsequent posts
  4. Number of replies

The calculation uses three site settings as multipliers:

Calculation steps

Calculate a top score for each topic by adding together the following:

  1. The number of views for each topic in the selected period multiplied by the log views multiplier
  2. The number of likes on the original post of the topic multiplied by the first post likes multiplier
  3. The lesser of:
    • The average likes per post (total likes on the topic divided by the number of posts)
    • The least likes per post multiplier value
  4. If the period has less than 10 posts, perform the following calculation:
    0 - ((10 - number of posts in the topic) / 20) * number of likes on the original post
    Otherwise, -10
  5. The number of posts on the topic

Viewing Top Topics

You can see an example of Top Topics on a Discourse site in the image below:

Example of Top Topics on a site
Example of Top Topics on a site2396×807 58.7 KB

Additional resources

For more technical details, you can refer to:

-- [params]
-- date :start_date = 26 apr 2020
-- date :end_date = 2 may 2020
-- double :log_views_multiplier = 2.0
-- double :first_post_likes_multiplier = 0.5
-- double :least_likes_per_post_multiplier = 3.0

WITH likes AS (
SELECT topic_id, SUM(like_count) AS count
FROM posts
WHERE created_at::date >= :start_date::date 
  AND created_at::date < :end_date::date
  AND deleted_at IS NULL
  AND NOT hidden
  AND post_type = 1
GROUP BY topic_id
),
op_likes AS (
SELECT topic_id, like_count AS count
FROM posts
WHERE created_at::date >= :start_date::date 
  AND created_at::date < :end_date::date
  AND post_number = 1
  AND deleted_at IS NULL
  AND NOT hidden
  AND post_type = 1
),
posts AS (
SELECT topic_id, GREATEST(COUNT(*), 1) AS count
FROM posts
WHERE created_at::date >= :start_date::date 
  AND created_at::date < :end_date::date
  AND deleted_at IS NULL
  AND NOT hidden
  AND post_type = 1
  AND user_id <> 0
GROUP BY topic_id
),
views AS (
SELECT topic_id, COUNT(*) AS count
FROM topic_views
WHERE viewed_at::date >= :start_date::date 
  AND viewed_at::date < :end_date::date
GROUP BY topic_id
),
category_definition_topic_ids AS (
  SELECT COALESCE(topic_id, 0) AS id FROM categories
),
top_topics AS(
SELECT 
topics.id AS topic_id,
topics.title,
topics.user_id, 
posts.count AS date_range_posts,
views.count AS date_range_views,
topics.views AS all_time_views,
topics.bumped_at,
(CASE
 WHEN topics.created_at::date < :start_date::date 
  AND topics.created_at::date >= :end_date::date
 THEN 0
 ELSE log(GREATEST(views.count, 1)) * :log_views_multiplier +
    op_likes.count * :first_post_likes_multiplier +
    CASE WHEN likes.count > 0 AND posts.count > 0
       THEN
        LEAST(likes.count / 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(posts.count, 1))
 END) AS score
FROM posts
INNER JOIN views ON posts.topic_id = views.topic_id
INNER JOIN likes ON posts.topic_id = likes.topic_id
INNER JOIN op_likes ON posts.topic_id = op_likes.topic_id
LEFT JOIN topics ON topics.id = posts.topic_id AND topics.deleted_at IS NULL
WHERE topics.deleted_at IS NULL
  AND topics.visible
  AND topics.archetype <> 'private_message'
  AND NOT topics.archived
  AND topics.id NOT IN (SELECT id FROM category_definition_topic_ids)
ORDER BY
  score DESC,
  topics.bumped_at DESC
)

SELECT * FROM top_topics WHERE score > 0

Last edited by @hugh 2024-07-02T04:15:57Z

Last checked by @hugh 2024-07-02T04:16:06Z

Check documentPerform check on document:
11 Likes
4

Is it possible to include ‘votes’ in this calculation?