All Questions
Tagged with sql-server stackexchange
50 questions
30
votes
2
answers
2k
views
Calculate amount of mugs StackExchange should give away so I'll receive one
Description
Calculate how many mugs StackExchange should give away(for graduation) to top reputed users on codereview.stackexchange.com so I'll also receive one. (output is divisible by 50).
Code
<...
- 4,531
29
votes
3
answers
439
views
"How can I make this SEDE query better?" (bad title finder)
It was suggested on Meta for July 2015 Community Challenge to make a bot that can find questions with bad titles. I wrote this query to help others find examples to help them make write their code.
...
- 20.4k
26
votes
6
answers
2k
views
Average time to first answer
Just trying out my T-SQL foo.
Wrote a script to get the average time (in seconds) to first answer for a specified language.
...
- 96.7k
25
votes
6
answers
693
views
Winterbash 2014 "Red Shirt" Estimation
Some fellow code reviewers (hi @Janos!) have been inquiring about a SEDE query to allow to check progress of the Red Shirt "hat" progression.
Try it here!
Background
Red Shirt
cast 5 ...
- 20.4k
24
votes
1
answer
114
views
Cross-posts from Stack Overflow to CR
What this code does it explained in detail in the top portion of the query.
I have left out part of the list of language tags as I felt it was just noise. You can see the full collection here.
You ...
- 20.4k
22
votes
1
answer
427
views
Calculating Lost Reputation
A discussion arose not long ago on the 2nd Monitor about how much reputation has been lost due to the reputation caps. There are a number of queries on the SEDE which try to address this:
A Users ...
- 97.5k
21
votes
3
answers
2k
views
Top users under age 30, sorted by age, with rank
In the pursuit of SQL enlightenment, I wrote a quick SEDE query to display Rep, Age and Overall ...
- 11.9k
19
votes
2
answers
395
views
Naruto, where are you?
During Winter Bash 2014, the Naruto hat was awarded to users who gave an answer that got accepted but hasn't received votes for 12 hours.
It was a weird thing, really:
why would someone ever accept a ...
- 112k
17
votes
2
answers
1k
views
Searching for bad Minecraft questions on Gaming.SE
To preface this post a little bit, I'll explain a little bit of the "backstory" here. The Stack Exchange site, Gaming.SE, also known as Arqade, often has a large influx of bad questions about the ...
- 15.8k
15
votes
1
answer
1k
views
A wounded Zombie finder, or: how to find the almost dead undead?
The Zombies are on their way to eat our statistics. Fortunately, we have a way to get rid of them: our votes. However, we need to find them first. The /unanswered section is too large and contains ...
- 19.3k
14
votes
2
answers
118
views
Bling Accounting - Badge summaries
A question was raised in chat (hi @malachi) about how you can count the number of badges of a given metal (bronze, silver, gold). This can be hard to do because the medal details are not stored in the ...
- 97.5k
13
votes
3
answers
207
views
SEDE Top Sponsors
I wanted to see the site's top sponsors - users that have paid bounties on questions that they didn't own.
I started off with a bounty-related existing query, selected the details into a subquery, ...
- 75.1k
12
votes
2
answers
110
views
Archaeologist Progression
One of the badges available on Stack Exchange is the Archaeologist badge. This is awarded for making 100 edits on posts that had been inactive (at the time of the edit) for 6 months.
There is no ...
- 97.5k
11
votes
3
answers
210
views
Tag badges per user SEDE query
I wanted to see where I lined up with other users on tag badges (I wanted to count just bronze but figured I would start with all of them to start out with).
Here is the query that I ended up with:
<...
- 28.7k
11
votes
2
answers
414
views
Percentage of questions that are unanswered or answered
I put together this simple query which calculates the total percentage of answered questions and unanswered questions. The formulas used to calculate these are shown below:
$$
\text{Answered ...
- 15.8k
11
votes
1
answer
341
views
CodeLess Questions query
As part of an exercise by Jamal to clean up old questions on Code Review that have no code in them, I helped by writing this query on Stack Exchange Data Explorer: CodeLess Questions
...
- 97.5k
11
votes
1
answer
116
views
The sniper: a Zombie searcher for specific tags
Sometimes, it's not enough to know what the easy prey is. You also need to know whether you will be able to hunt it down. What good is an almost turned haskell zombie if you cannot read the functional ...
- 19.3k
10
votes
3
answers
407
views
Popular questions by view count
I made this query to create a graph of a user's popular questions and the view count on that question. It allows for a minimum of 500 views, and a score of ...
- 15.8k
10
votes
4
answers
191
views
SEDE-Query for Rep-Trivia
The code should be relatively self-explanatory. I was wondering how much rep the Top Voters on sites did "generate" on a site, in comparison to how much rep they "own".
Therefore I created a little ...
- 25.3k
10
votes
2
answers
127
views
Forgotten zombie killers
Zombies are questions with no upvoted answers.
I came up with this SEDE query to find answers with zero votes on questions that have no accepted answer and no answers with greater than zero score.
In ...
- 112k
10
votes
2
answers
119
views
SE Data Explorer Query: Average score for questions and answers, by tag
Being curious to see which tags on this site get the most attention, I developed a Stack Exchange Data Explorer query:
...
- 144k
10
votes
2
answers
404
views
Who are these lurkers?
Last night in The 2nd Monitor there was a discussion about postless users - I wanted to see how many there are, whether they're one-timers that showed up once, registered and never came back, and ...
- 75.1k
9
votes
1
answer
132
views
The seasoned Zombie sniper: a query to find unanswered questions with specific tags
The query (on SEDE)
This query builds upon the previous and includes the suggestions by rofl as well as the now mentioned activity index.
...
- 19.3k
9
votes
1
answer
454
views
Load and parse Stack Exchange data dump XML into DB table
I have put together a stored procedure to load and parse the Stack Exchange Data Dump into a relational database (akin to Stack Exchange Data Explorer). Each site has 8 XML files like these:
The ...
- 20.4k
8
votes
2
answers
152
views
Finding questions to answer
In light of the recent "SQL frenzy" of sorts in The 2nd Monitor, I decided to take a stab at writing my own SEDE query. Essentially, what it does is find questions that could be answered based on the ...
- 15.8k
8
votes
2
answers
108
views
Query for your Answer Progress
I was inspired to write a query that produced a graph mapping the perceived quality of your Stack Exchange answers over time.... in other words, "Are your answers getting better, or worse?"
Because ...
- 97.5k
8
votes
2
answers
210
views
What are my highest activity streaks?
I have written the following query to figure out activity streaks on a per-user basis. I find it... Ugly... And would love to improve it!
Limitations
Those are explained as commented text at the very ...
- 20.4k
8
votes
1
answer
115
views
The opposite of CodeLess Questions query
What's the opposite of CodeLess Questions?
Perhaps CodeOnly Answers? (Also known as code dumps.)
Inspired by the opposite question, here's the opposite query:
...
- 112k
7
votes
2
answers
104
views
Top Python badged users from NYC
I forked this StackExchange Data Explorer (SEDE) query from another one, and made it a bit more complex. I'm looking for feedback on my style, as well as answers to other questions (see below).
...
- 1,568
7
votes
2
answers
73
views
1st questions with answers within X hours
I recently challenged @Hosch520 with writing a query as such:
Find first questions with answers posted within 24 hours
And he did great. I figured I would have a go at it too. Turns out I learned ...
- 20.4k
7
votes
1
answer
86
views
Zombie killers ranking
Code Review has a Zombie Problem and, in comparison to The Walking Dead TV Show, our survival doesn't depend on killing them all day every day.
As a potential solution, I though it would be ...
- 12.4k
7
votes
2
answers
172
views
Follow up to Top Active Answerers on Stack Exchange site
This is a follow up to Top Active Answerers on a Stack Exchange site
Actual SEDE Query --> Top Active Answerers on site
Do I have a good understanding of the RANK ...
- 28.7k
7
votes
2
answers
56
views
Retrieve high views per day questions with few answers
I've been messing around with the SE Data Explorer and I've come up with a query that basically returns information about questions that have a high number of views per day, that have few or no ...
- 11.7k
7
votes
1
answer
125
views
Finding answers where happy customers commented "thanks"
To find answers where users expressed their gratitude (by mentioning "thank" in comments),
I put together this SEDE query (latest):
...
- 112k
7
votes
1
answer
65
views
Aggregating Conditional Sums
I have recently forked/rewritten a SEDE query (here) that aims at figuring out where a site stands in terms of avid users and distribution of reputation scores, compared to a specific target (number ...
- 75.1k
7
votes
1
answer
86
views
Fascinating, ma'am
Finding a famous question with high score I haven't voted on yet was proving to be a bit difficult for me. So I came up with this simple SEDE query to find good candidates:
...
- 112k
7
votes
1
answer
90
views
Stack Exchange Data Explorer query to find upvoted questions with only downvoted answers
I have written a SQL query against the Stack Exchange Data Explorer that identifies answered questions with a positive score for which all the answers have a negative score (this was an answer to this ...
- 173
6
votes
2
answers
120
views
Find how much reputation a user had on a given date
I saw this question on MSE and went ahead and wrote a solution to it:
https://meta.stackexchange.com/questions/313561/determining-users-reputation-as-of-particular-date
This calculates the ...
- 330
6
votes
2
answers
145
views
Finding questions to answer - Part 2
I've refactored my previous question finder, and added multi-tag support, and support for searching for keywords in the question titles. Essentially, what it does is find questions that could be ...
- 15.8k
6
votes
1
answer
82
views
Finding which users have the most questions with unaccepted answers
I was looking at a few profiles this afternoon and noticed a few users had plenty of open questions. This led me to wonder which users actually had the highest amount of questions with unaccepted ...
- 6,365
6
votes
1
answer
109
views
SEDE query to find users with exactly one post in a particular tag
I'm doing some research for a meta post on Gardening.SE (where I'm a pro tempore moderator). I'm using the Stack Exchange Data Explorer (SEDE) to find information about first time users who post ...
- 859
5
votes
3
answers
259
views
Find Users by Most Badges
I very infrequently use SQL. I need more experience, so I have created a data explorer query to find those users with most of any particular badge (have to edit the query itself right now to alter it)....
- 1,568
5
votes
1
answer
90
views
Searching posts/comments by keywords
I've put together another simple query that allows you to search post/comments with specific keywords. At the moment, only post bodies are checked.
...
- 15.8k
5
votes
1
answer
144
views
SQL query with dynamic unpivot+pivot for cross product
In this Data Explorer query I am trying to do the following:
For each tag:
Compute sum of answer scores in this tag ("S")
Compute count of answers in this tag ("A")
For each tag class (Bronze, ...
- 262
5
votes
1
answer
112
views
Finding bad Narutos
While looking for Naruto posts
(= overlooked accepted answers well worthy of upvotes),
I noticed some odd false positives,
selfie answers,
where the user who asked the question accepted his own answer ...
- 112k
4
votes
1
answer
48
views
Tag wikis under a certain length
I wrote a SEDE query to list all tag wikis with a body or an excerpt under a given number of characters. I've used this to find empty wikis or wikis with very little information. The tags can be ...
- 2,661
3
votes
2
answers
299
views
Top Active Answerers on a Stack Exchange site
I created this query back in January to find the top active answerers on Code Review and thought that it might be a good idea to get other opinions on my SQL coding using a database that we can all ...
- 28.7k
3
votes
2
answers
47
views
Percentage of questions that are unanswered or answered - Part 2
I've refactored my previous query and added better checks when selecting the data. The query link can be found here. The formulas used to calculate the two percentages are the same, and can be seen ...
- 15.8k
2
votes
1
answer
261
views
How to make this query better (e.g. avoid repeating subquery in a query)?
Stack Exchange provides access to query their MS SQL databases. They have a table Posts where each post is either a question or an answer (see the schema on right ...
- 121
1
vote
1
answer
119
views
Get average post score of TOP 10 tags on StackOverflow using Stack Exchange Data Explorer
I want to use the Stack exchange data explorer in order to display some basic information about the top tags.
The query should print the number of questions and answers, the average number of answers ...
- 129