Stack Overflow’s forum is dead thanks to AI, but the company’s still kicking… thanks to AI – Sherwood News (and a few links on SEDE – Stack Exchange Data Explorer)

Interesting article: [Wayback/Archive] Stack Overflow’s forum is dead thanks to AI, but the company’s still kicking… thanks to AI – Sherwood News. with this important quote: The complex questions still get asked on Stack because there’s no other place. If the LLMs are only as good as the data, which is typically human curated, we’re one of the best places for that, if not the best for technology. I wonder about how far it has declined now, and also think these are reasons for the decline as well: a lot of fundamental questions for each topic have already been asked few new programming languages gained popularity over the last decade (I think golang was the last major one) discussions on GitHub and to a lesser extent GitLab have taken over a lot of traffic toxic behaviours on mainly the Stack overflow and meta site I mentioned in The toxic reactions to “What would be good alternatives to Stack Overflow for questions about software solutions to drive hardware? – Meta Stack Overflow” Anyway, the graph in that post is just a sexy version of a query you can create yourself on the SEDE (Stack Exchange Data Explorer). That’s why I included both below. Graph: Stack Overflow’s Forum’s Decline Started Years Ago, But Al Killed The Platform For Good; Horizontal data: year; Vertical data: Number Of Questions Asked [Monthly, including deleted questions]; Rise from 2008-24, peak during Covid-19 in 2021/2022, decline from 2024 when ChatGPT got launched If you are like me, you also want other measures, like seeing answers and comments: are these also dropping, if so do they drop at the same rate, and what’s the ratio over time of answers over questions, and comments over questions and answers?

Generating graphs like these yourself If you want to regenerate a very similar graph, check out the query [Archive] Questions per month including deleted – Stack Exchange Data Explorer SELECT DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS [Month], COUNT(*) AS [Questions] FROM PostsWithDeleted WHERE PostTypeId = 1 GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) ORDER BY [Month] ASC That was my starting point after a quick search, and the base of all my queries below. The above query resulted in this graph: [Archive] Questions per month including deleted – Stack Exchange Data Explorer (graph) [Wayback/Archive] 536362203-4db75341-a0c7-4b64-a6ec-e67f80575005.png (1003×535) Answers per month including deleted The is a similar graph as the above one, but now showing answers instead of questions derived from the above Questions query: Answers per month including deleted – Stack Exchange Data Explorer: [Wayback/Archive] 537280204-e8929110-74f7-4fd5-a327-aaf32d270914.png (983×566) The query is different in just two places: the count(*) alias and the PostTypeId value. SELECT DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS [Month], COUNT(*) AS [Answers] FROM PostsWithDeleted WHERE PostTypeId = 2 GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) ORDER BY [Month] ASC That opened a quest to get this graph: Combined graph adding all post types What I needed for this is aggregate the various post types (question, answer) and group by month. This is a tad more complex than Count(*) where PostTypeId=# queries above. So I did some digging, where the results… were NOT from the Stack Exchange network (: I started looking for transaction types and months because I know that is a common scenario in databases.

Google Search returned relevant results, but Duck Duck Go didn’t. [WaybackFailed/ArchiveSave] (Cannot be archived) sql count transaction types group by month – Google Search [Wayback/Archive] sql count transaction types group by month at DuckDuckGo had no good results [Wayback/Archive] sql query to generate counts by month (no more recent Wayback Machine archivals because of #ThisIsWhyWeCantHaveNiceThings with JavaScript and URL rewrites) You can do some things with “GROUPING” (not included below) to make the subtotals and totals more apparent but, considering you posted no schema nor data, this should get you started once you make the appropriate substitutions for table and column names as required… SELECT d.Company, d.[Year], SUM(CASE WHEN [Month] = 01 THEN Transactions ELSE 0 END) AS Jan, SUM(CASE WHEN [Month] = 02 THEN Transactions ELSE 0 END) AS Feb, SUM(CASE WHEN [Month] = 03 THEN Transactions ELSE 0 END) AS Mar, SUM(CASE WHEN [Month] = 04 THEN Transactions ELSE 0 END) AS Apr, SUM(CASE WHEN [Month] = 05 THEN Transactions ELSE 0 END) AS May, SUM(CASE WHEN [Month] = 06 THEN Transactions ELSE 0 END) AS Jun, SUM(CASE WHEN [Month] = 07 THEN Transactions ELSE 0 END) AS Jul, SUM(CASE WHEN [Month] = 08 THEN Transactions ELSE 0 END) AS Aug, SUM(CASE WHEN [Month] = 09 THEN Transactions ELSE 0 END) AS Sep, SUM(CASE WHEN [Month] = 10 THEN Transactions ELSE 0 END) AS Oct, SUM(CASE WHEN [Month] = 11 THEN Transactions ELSE 0 END) AS Nov, SUM(CASE WHEN [Month] = 12 THEN Transactions ELSE 0 END) AS [Dec],

SUM(Transactions) AS Total, SUM(Transactions)/24 AS Average FROM (--Derived table "d" finds count for year and month SELECT Company, [Year] = DATEPART(yy,TransactionDate), [Month] = DATEPART(mm,TransactionDate) Transactions = COUNT(*) FROM yourtable GROUP BY Company, DATEPART(yy,TransactionDate), DATEPART(mm,TransactionDate) ) d GROUP BY d.Company, d.[Year] WITH ROLLUP ORDER BY d.Company, d.[Year] …and, in a properly indexed environment, will process millions of rows in scant seconds (ie. 4 million rows in about 5-7 seconds). –Jeff Moden [Wayback/Archive] How to Summarize Monthly Transactions by Country in SQL | Step-by-Step Guide | by Data Science Wallah | Medium In this tutorial, we will solve the SQL problem “Monthly Transactions I” from LeetCode. This problem will test your skills in grouping data by date and country, as well as filtering and aggregating transaction data based on specific conditions. This is an excellent problem for practicing SQL aggregation functions like COUNT(), SUM(), and GROUP BY. … Here’s the complete SQL query to solve the problem: SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country, COUNT(*) AS trans_count, SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count, SUM(amount) AS trans_total_amount, SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount FROM Transactions GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country; The tricks used/knowledge needed: Combine SUM and CASE WHEN to count relevant values as there is nou COUNT(WHEN …) GROUP BY cannot use aliases from the SELECT portion So in order to get the counts for the various PostTypes, we need to add a column that counts for each PostTypes value.

The PostTypes are these (thanks [Wayback/Archive] Show all types – Stack Exchange Data Explorer, see more about it further below): Id PostType ---- ------------------- 1 Question 2 Answer 3 Wiki 4 TagWikiExcerpt 5 TagWiki 6 ModeratorNomination 7 WikiPlaceholder 8 PrivilegeWiki That resulted in this query: Questions, answers and other post types per month including deleted – Stack Exchange Data Explorer (3 seconds) SELECT DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS [Month], SUM(CASE WHEN PostTypeId = 1 THEN 1 ELSE 0 END) AS [Questions], SUM(CASE WHEN PostTypeId = 2 THEN 1 ELSE 0 END) AS [Answers], SUM(CASE WHEN PostTypeId = 3 THEN 1 ELSE 0 END) AS [Wikis], SUM(CASE WHEN PostTypeId = 4 THEN 1 ELSE 0 END) AS [TagWikiExcerpts], SUM(CASE WHEN PostTypeId = 5 THEN 1 ELSE 0 END) AS [TagWikis], SUM(CASE WHEN PostTypeId = 6 THEN 1 ELSE 0 END) AS [ModeratorNominations], SUM(CASE WHEN PostTypeId = 7 THEN 1 ELSE 0 END) AS [WikiPlaceholders], SUM(CASE WHEN PostTypeId = 8 THEN 1 ELSE 0 END) AS [PrivilegeWikis], COUNT(*) AS [AllPostTypes] FROM PostsWithDeleted GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) ORDER BY [Month]

ASC with this graph: Questions, Answers, Wikis TagWikiExcerpts, TagWikis, ModerationNominations, WikiPlaceholders, PrivilegeWikis, AllPostTypes including deleted ones per month [Wayback/Archive] 539268768-c1955f6e-70b2-4025-8a2b-02534b08f720.png (979×525) This also gives a rough view of answers per question: until roughly 2018, that was above 1, and from 2020 on it got below 1. Comments by month graph In created this to try creating a second “combined” graph below. The query is this: Comments count by Month – Stack Exchange Data Explorer SELECT DATEFROMPARTS(YEAR(Comments.CreationDate), MONTH(Comments.CreationDate), 1) AS [Month], COUNT(Id) AS CommentIdCount FROM Comments GROUP BY DATEFROMPARTS(YEAR(Comments.CreationDate), MONTH(Comments.CreationDate), 1) ORDER BY [Month] ASC and the graph: CommentIdCount by month [Wayback/Archive] 537654387-b7af32a9-68ef-4b06-be89-a36dc620f105.png (979×523) Combined graph adding comments to all post types The above question/answer ratio estimate got me thinking: what about comments and the ratio to posts – how did these evolve over time? The last query above took about 3 seconds. The final one about 7 seconds as it needs two more queries, as you can see in Comments for questions, answers and other posts per month including deleted – Stack Exchange Data Explorer: WITH PostCommentCounts AS ( SELECT COUNT(Id) AS CommentIdCount, PostId FROM Comments GROUP BY PostId ) SELECT DATEFROMPARTS(YEAR(PostsWithDeleted.CreationDate), MONTH(PostsWithDeleted.

CreationDate), 1) AS [Month], SUM(CASE WHEN PostTypeId = 1 THEN 1 ELSE 0 END) AS [Questions], SUM(CASE WHEN PostTypeId = 2 THEN 1 ELSE 0 END) AS [Answers], SUM(CASE WHEN PostTypeId = 3 THEN 1 ELSE 0 END) AS [Wikis], SUM(CASE WHEN PostTypeId = 4 THEN 1 ELSE 0 END) AS [TagWikiExcerpts], SUM(CASE WHEN PostTypeId = 5 THEN 1 ELSE 0 END) AS [TagWikis], SUM(CASE WHEN PostTypeId = 6 THEN 1 ELSE 0 END) AS [ModeratorNominations], SUM(CASE WHEN PostTypeId = 7 THEN 1 ELSE 0 END) AS [WikiPlaceholders], SUM(CASE WHEN PostTypeId = 8 THEN 1 ELSE 0 END) AS [PrivilegeWikis], SUM(PostCommentCounts.CommentIdCount) AS [Comments], COUNT(PostsWithDeleted.Id) AS [AllPostTypes] FROM PostsWithDeleted LEFT JOIN PostCommentCounts ON PostCommentCounts.PostId = PostsWithDeleted.Id GROUP BY DATEFROMPARTS(YEAR(PostsWithDeleted.CreationDate), MONTH(PostsWithDeleted.CreationDate), 1) ORDER BY [Month] ASC with this graph: Comments for the Questions, Answers, Wikis TagWikiExcerpts, TagWikis, ModerationNominations, WikiPlaceholders, PrivilegeWikis, AllPostTypes including deleted ones per month (correlated by post) [Wayback/Archive] 537322714-a43a36be-4a30-421a-8235-74d7d865a0cb.png (978×523) I wasn’t really sure what the best way was to add the count of comments (which are in a different table) into the results. The above query correlates them on PostId. This could also be done by [Month]. Since they are correlated, part of the aggregation needs to be done in a separate query.