6

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?

4

I found a way - and I created a dashboard you can use to get the top questions for any [tag] and [tag, subtag].

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?

enter image description here

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.

enter image description here

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:

enter image description here

Java is gone from the top 30 - and Python now takes a huge chunk. The more interesting part here is that you can go check the top current questions for any tag. Let's compare TensorFlow with JavaScript:

enter image description hereenter image description here

7 of the top 10 TensorFlow questions deal with installation and CPU/GPU issues. That's a strong signal. Meanwhile most @JavaScript questions look stable, except 2 rapidly rising ones. And you might want to look deeper. For example MongoDB+Java questions

enter image description here

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

enter image description here

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

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .