I also wanted to be able to work out the total number of points given from a particular tag between particular dates (luckily the accepted answer combined with the "score" column is sufficient for this). I wanted to know if an answer given was the accepted answer (to work out accepted answer rates). Now because I wanted to make this a useful general query there were a few other things I wanted. The other thing I wanted was a word count for a given tag. Not only top of the board but by a decent margin, I consider that challenge complete!Įvery single answer given on a particular tag by a user OwnerUserId IS NOT NULL ORDER BY DESC, DESC, DESCĪs you can see the narcissist in me is happy with those results (I am Graham Ritchie, so now you know my real name □). OwnerUserId ) AS FROM allUsers au LEFT JOIN users u ON u. PostId ), allUsers AS ( SELECT OwnerUserId FROM questsByTags UNION SELECT OwnerUserId FROM answersByTags UNION SELECT OwnerUserId FROM commntsByTags ) SELECT au. UserId AS FROM Comments c INNER JOIN ( SELECT Id FROM questsByTags UNION ALL SELECT Id FROM answersByTags ) AS allPosts ON allPosts. OwnerUserId FROM Posts a INNER JOIN questsByTags qbt ON qbt. TagName = TagName ) ), answersByTags AS ( SELECT a. OwnerUserId FROM Posts q INNER JOIN PostTags pt ON q. It allows you to grab all of the questions, answers and comments on a given tag, ranked by number of answers given initially.ĭECLARE tagName varchar ( 255 ) SET tagname = rtrim ( # TagName : string # ) DECLARE start varchar ( 255 ) SET start = rtrim ( # Start : string # ) DECLARE end varchar ( 255 ) SET end = rtrim ( # End : string # ) WITH questsByTags AS ( SELECT DISTINCT q. Put it like this, it really highlighted my poor SQL skills! □ This query took me ages to put together, referencing several other queries other people had writeen to piece it together. Number of comments, questions and answers ranked by number of answers So here are a couple of queries I put together so I could analyse my progress: It allows you to analyse the Stack Overflow database and grab some interesting statistics / info. Luckily Stack Overflow has a great feature that few people seem to use / know about Query Stack Overflow () I find answering questions to be the best way to learn (and reinforce any imposter syndrome or conversly, make sure I don't suffer from the Dunning Kruger effect! hehe.Īnyway, I know I have been very active answering on those tags but the narcissist in me wanted to know how I compared to everyone else. Since September 2019 I have tried to give the best answers I can on the and tags on Stack Overflow, linking with my two passions in web development, load speed and inclusivity. It is part of a personal challenge I set myself nearly two years ago. I have answered 300 questions (and most are answered correctly □) on the tag. I recently reached a milestone on Stack Overflow.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |