This is a reference guide for describing how topics filtered by “Top” are determined to be “Top Topics”
All Discourse sites have the ability to sort topics by “Top”.
All “Top” topics in this section are all assigned a “Top Score” that is calculated based on: likes on the first post, likes on the rest of comments, number of replies and number of views, for the selected period of time.
Topics are then displayed with the topics having the highest “Top Score” appearing at the top of the list.
Example of Top Topics on a site:
Top Score Calculation
The calculation for a topic’s “Top Score” is as follows:
- Pull the following values from the site settings:
top topics formula log views multiplier
top topics formula first post likes multiplier
top topics formula least likes per post multiplier
- Calculate a top score for each topic by adding together the following:
- Number of views each topic has for the selected period multiplied by the
top topics formula log views multiplier
- Number of likes on the original post of the topic multiplied by the
top topics formula first post likes multiplier
- The lesser of:
- The Number of likes on the topic divided by the number of posts of the topic (the average number of likes per post)
- The
top topics formula least likes per post multiplier
value
-
IF the period has less than 10 posts then:
0 - ((10 - number of posts in the topic) / 20) * number of likes on the original post
- Else - 10
- The number of posts on the topic
Source Code
The Ruby source code for the top calculation can be found here:
Data Explorer Query
This Data Explorer query is pretty much identical to the Top page, just with a custom date range and the ability to get current results at the click of a button. You can use this query to see the exact “Top Score” for each topic along with other information about topics filtered by “Top”.
-- [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