Stack Overflow provides the total number of pageviews per question - but it doesn't tell you if those views are historical or if the question is still current. How can I find the current top questions?
I found a way - and I created a dashboard you can use to get the top questions for any [tag] and [tag, subtag].
- Article: https://medium.com/@hoffa/finding-the-real-top-stack-overflow-questions-aebf35b095f1
- Interactive dashboard: https://datastudio.google.com/open/1UUTtohIB1fBH7e8An-6AhEn4AyU3eMjd
Basically I'm keeping all the provided Stack Overflow dumps and comparing the number of pageviews between each snapshot.
When looking at Stack Overflow pageviews it's really important to be able to see the current #, not just the historical. That hasn't been easy - until now. For example, look at the top 10 Stack Overflow questions ever. What's missing?
Python is missing! There are no Python questions on the top 10 ever Stack Overflow questions. So let's find instead the top 10 for last quarter.
Ahh... so "how to undo the most recent commit in Git" is the top Stack Overflow question ever, and also the current one. But now 40% of top questions are Python related. And that's new. Let's see how the composition of the top 30 changes:
If you want to go deeper, you can go to BigQuery and ask questions, like: "What are the top Dataflow questions"?
#standardSQL SELECT title, quarter_views, view_count FROM `fh-bigquery.stackoverflow_archive_questions.merged` WHERE 'google-cloud-dataflow' IN UNNEST(tags) AND quarter='2018-12-01' ORDER BY quarter_views DESC LIMIT 10
Or to find the top current questions that haven't been updated in more than a year:
#standardSQL WITH top_questions AS ( SELECT id, title, quarter_views, view_count FROM `fh-bigquery.stackoverflow_archive_questions.merged` WHERE 'google-cloud-dataflow' IN UNNEST(tags) AND quarter='2018-12-01' ), latest_answer AS ( SELECT parent_id, DATE(MAX(COALESCE(last_edit_date, last_activity_date, creation_date))) answer_last_edit_date FROM `bigquery-public-data.stackoverflow.posts_answers` b GROUP BY parent_id ) SELECT SUBSTR(title, 0,80) title, quarter_views, view_count, answer_last_edit_date FROM top_questions a JOIN latest_answer b ON a.id=b.parent_id WHERE DATE_DIFF(CURRENT_DATE(), answer_last_edit_date, DAY)>360 ORDER BY quarter_views DESC LIMIT 10