Visualization of voting trends on Stack Overflow: 2009-14

This is a visualisation of how the number of answers/answerers and votes/score of answers has evolved over time on Stack Overflow.

The visualisation is produced from the Sept' 2014 data dump. Answers are binned by the month they were posted in and only the votes cast at most 1 month after the posting of an answer are counted. The top 100 tags (by count) were chosen for illustrative purposes.

It shows that while the number of answers and answerers have been constantly increasing for most popular tags, the number of votes that answers receive in their first month has been on the wane. Hence, getting votes on answers is now becoming harder and harder: the number of answerers (and answers) is increasing at a rate faster than the votes (and score) the posts gather in the first month.

The URL saves the current settings of the graph controls so you can share it with anyone you like and they will see the same settings are you are seeing.

This was the SQL used to generate the data:

      
WITH TopTags AS (
     SELECT id AS tagId,
            tagName,
            Count
     FROM Tags
     ORDER BY Count DESC LIMIT 100),
   AnswerStats AS (
     SELECT TT.tagName                         AS tagname,
            extract(year FROM A.CreationDate)  AS year,
            extract(month FROM A.CreationDate) AS month,
            COUNT(*)                           AS answers,
            COUNT(DISTINCT A.OwnerUserId)      AS answerers
     FROM Posts A
         JOIN AllPostTags APT ON (APT.PostId = A.Id)
         JOIN TopTags TT      ON (TT.tagId = APT.tagId)
     WHERE A.PostTypeId = 2
        AND A.OwnerUserId IS NOT NULL
        AND A.OwnerUserId > 0
        AND A.CreationDate < '2014-08-01'
     GROUP BY TT.TagName,
              extract(year FROM A.CreationDate),
              extract(month FROM A.CreationDate)),
   VoteStats AS (
     SELECT TT.tagName                         AS tagname,
            extract(year FROM P.CreationDate)  AS year,
            extract(month FROM P.CreationDate) AS month,
            COUNT(*)                           AS votes,
            SUM(CASE WHEN V.VoteTypeId = 2
                THEN 1 ELSE -1 END)            AS score
     FROM Votes V
         JOIN Posts P         ON (P.Id = V.PostId)
         JOIN AllPostTags APT ON (APT.PostId = V.PostId)
         JOIN TopTags TT      ON (TT.tagId = APT.tagId)
     WHERE V.CreationDate < P.CreationDate + '1 month'
        AND P.CreationDate < '2014-08-01'
        AND P.PostTypeId = 2
        AND V.VoteTypeId IN (2, 3)
     GROUP BY TT.TagName,
              extract(year FROM P.CreationDate),
              extract(month FROM P.CreationDate))
SELECT * FROM AnswerStats NATURAL FULL OUTER JOIN VoteStats
ORDER BY tagname, year, month;
      
    

The tables were generated from any Stack Overflow data dump using Networks-Learning/stackexchange-dump-to-postgres.