Data explorer query to list the longest "estimated read time" topics?

Sorry for the late answer. I got caught up in the question about how to organize Data Explorer queries on the site. Using the data-explorer tag seems like the ideal solution, but topics that contain a Data Explorer query will need to be tagged by a user with TL3 status.

I think that something like the following query will give you the information you’re looking for:

SELECT
topic_id,
category_id,
SUM(total_msecs_viewed) / 60000  AS estimated_minutes_read
FROM topic_users tu
JOIN topics t ON t.id = tu.topic_id
WHERE t.deleted_at IS NULL
AND t.archetype = 'regular'
GROUP BY tu.topic_id, category_id
ORDER BY estimated_minutes_read DESC
LIMIT 100

The LIMIT 100 statement in the last line of the query could be adjusted or removed if you want more results to be returned.

Interestingly, the topic with by far the most recorded read time on Meta is Setup DiscourseConnect - Official Single-Sign-On for Discourse (sso). It’s currently at 126048 minutes.

3 Likes