Hi everyone,
Is it possible to create a data explorer query which can list the “top X” topics by “estimated read time” ?
I’d love to know which topics on our Discourse are the most time consuming to read 
(And off topic, further to this post by @simon , I can’t seem to add a data-explorer tag to this post?)
I think the approach I suggested in that topic needs to be improved. One problem with it is that only TL3 and above users can tag posts on Meta. That means that the majority of the site’s users wouldn’t be able to follow my instructions. The other issue is that we’ll end up with both unanswered topics and answered topics having the data-explorer tag. That won’t help much with searching for queries.
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.
Hi @simon
Is that formula correct?
If I pick four or five at random, and compare the estimated read time column result in this query, to the estimated read time in the topic itself, I’m getting two very different numbers? 
It looks like that query gives you the Topics that have been read for the most time, rather than the Topics that take the longest to read?
Ah, that could explain the issue.
I’m guessing total_msecs_viewed is the wrong column to use here?
You can use the average time that the users take to read the topic.
In that case, you can just change the SUM function to AVG, it would look like this:
SELECT
topic_id,
category_id,
AVG(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
Thanks for the suggestion @michebs but I’m afraid that one is way off the mark too.
A few examples:
| What the query says | What the topic says |
|---|---|
| 438 | 61 |
| 353 | 58 |
| 335 | 40 |
| 196 | 24 |
But that would mean, on average, a person takes 438 minutes to read that top topic? That seems unlikely. This may sound silly, but did you have enough 0s in 60,000?
Edit: Or maybe the AVG includes all the re-readings of a topic too? So once through would be 61 minutes, but actually users spend on average 438 minutes in there.
Though now I’m quite interested to know how the Estimated Read Time is worked out for the Summary, as ideally you’d want those to match. Even shrinking those by a factor of ten would only ballpark it.
Yes, exactly 
I had a little search, and found this "There are 84 replies with an estimated read time of 0 minutes." - #9 by nbianca.
I struggle with deciphering these things, but it seems it’s uses a word count x time figure (plus a minimum time to cover posts with no words, like images).
There was also this one which gave a hint as to what the final value may be called: (though its old, so may have changed?)
Not massively helpful, I’m sure, but thought I’d share just in case. 
I hope you get the answers you’re looking for. 
لقد ألقيت نظرة أخرى على هذا، ويبدو (في أبسط صوره) أنه عدد كلمات الموضوع مضروبًا في إعداد المسؤول “عدد كلمات وقت القراءة” (الافتراضي 500 كلمة/دقيقة). لذلك أعتقد أن هذا الاستعلام سينتج “أطول المواضيع للقراءة” X الأفضل:
-- [params]
-- integer :limit = 10
SELECT t.id as topic_id, (t.word_count)/500+1 AS estimated_read_time
FROM topics t
WHERE t.word_count IS NOT NULL
AND t.archetype = 'regular'
ORDER BY t.word_count DESC
LIMIT :limit
على الرغم من وجود بديل “4 ثوانٍ كحد أدنى” أيضًا: (عدد المشاركات × 4) / 60. وهو موجود لحساب مواضيع الصور التي لا تحتوي على عدد كلمات. لذا فهو يعمل بكلتا الطريقتين، ويعرض أيهما أكبر. لكنني لم أكتشف بعد كيفية إضافة ذلك. 
للأسف، ليس لدي موقع كبير بما يكفي لاختباره بشكل صحيح. بدا أنه يعمل على عينة اختبار صغيرة، ولكنه قد يحتاج إلى تعديل.
تعديل: أضفت معلمة “limit” لجعله أقرب إلى مواصفات OP. 
يا إلهي، أعتقد أنه فهمها!
@JammyDodger لقد قمت بتشغيل استعلامك، إليك بعض لقطات الشاشة كمرجع.
أولاً، “أفضل 10”:
وبالفعل:
هناك بعض الأرقام التي لا تتطابق تمامًا، لكنها قريبة جدًا!
يبدو أنني بالتأكيد بحاجة إلى معرفة كيفية إضافة صورة واحدة. لم أستسلم بعد. 
لقد حاولت مرة أخرى. لست متأكدًا بنسبة 100% من هذا، حيث ليس لدي عينة كبيرة بما يكفي لاختبارها، لكنها التقطت مواضيع الاختبار الخاصة بي.
-- [params]
-- integer :limit = 10
WITH read_time AS (
SELECT t.id as topic_id,
(t.word_count)/500+1 as word_count_time,
(t.posts_count*4)/60+1 as post_count_time
FROM topics t
WHERE t.word_count IS NOT NULL
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
)
SELECT topic_id, CONCAT (CASE WHEN word_count_time > post_count_time THEN word_count_time ELSE post_count_time END, ' min') AS estimated_reading_time
FROM read_time
ORDER BY estimated_reading_time DESC
LIMIT :limit