8
votes
Accepted
How to escape single quotes in SEDE ##keyword## search?
SEDE Parameters are plain text replacements.
Adding a type either adds client-side validation (for int) or wraps your value in 'quotes'. There is no magic.
To get your example query to work you have ...
reneMod
- 91.4k
7
votes
How to use Data Explorer to find the most prolific closers on a site?
With the newer version of SQL Server the function JSON_VALUE got added. That makes the parsing of that JSON payload way easier.
Lazy as we are we string_split 0,1,3,4,5,6 on the comma to act as a set ...
reneMod
- 91.4k
5
votes
Accepted
How to get tags of questions answered by a user in Stack Exchange Data Explorer
A query like this needs a lot of JOINs, from users to answers to questions to tags. I wasn't convinced that would work on a database as large as Stack Overflow's, but this query nevertheless returns ...
GlorfindelMod
- 253k
5
votes
Find questions where any of the tags are in the title or body?
If you don't fancy joining over 2 tables as shown in the answer by Glorfindel you can String_Split the Tags field (which will hold <c#><python> in your example) and then juggle a bit with ...
reneMod
- 91.4k
5
votes
Accepted
Find questions where any of the tags are in the title or body?
You have to JOIN with the PostTags and Tags tables, e.g. like this query:
SELECT TOP 10 p.Id AS [Post Link], p.Tags
FROM Posts AS p
INNER JOIN PostTags AS pt
ON p.Id = pt.PostId
INNER JOIN ...
GlorfindelMod
- 253k
5
votes
How do I modify this query to search for specific tags?
Answers don't have tags, Questions have.
Joining with PostTags on the Posts.ParentId should do the trick for that snippet:
WITH
Answers AS (
SELECT *
FROM Posts as P
...
reneMod
- 91.4k
4
votes
How do I modify this query to search for specific tags?
The Tags field looks like this: <java><unicode><comments> so you need to use LIKE. Here's how I did it (matching on the questions' tags):
WHERE
Questions.PostTypeId = 1 -- ...
- 54.6k
4
votes
Accepted
Any way to auto-format SQL in Stack Exchange Data Explorer?
There is currently no such feature in the Data Explorer, at least not one that is documented in the help page.
Having said that, it's indeed a great feature-request, and let's hope the SE devs pick up ...
- 7,064
4
votes
What post query does Stack Overflow fire to get a question's details with answers, tags, votes, etc.?
All the other data is stored in different tables. So you would not run one query, but several.
For example, to get comments for a given Post you would execute this query:
SELECT * # Replace the ...
reneMod
- 91.4k
3
votes
Accepted
How do you join between Posts and Users in the Stack Overflow database?
You would use Users.ID and join with the Posts.OwnerUserId.
The Users.AccountId references the Global Network Account and can be used to link Users across databases/sites.
Here is an example query ...
reneMod
- 91.4k
3
votes
Accepted
Problems filtering rows with Stack Exchange Data Explorer (SEDE)
Something like this would work, using a temporary table:
create table #badges (userid int, name nvarchar(50) collate SQL_Latin1_General_CP1_CS_AS);
insert into #badges
select top 100 userid, name
...
GlorfindelMod
- 253k
2
votes
Accepted
Why doesn't LIMIT work in Stack Exchange Data Explorer?
The Data Explorer is using T-SQL, Microsoft's SQL dialect for SQL Server. It does not have the LIMIT command, instead, you have to use TOP:
select TOP 1 ownerUserId, creationDate from posts
GlorfindelMod
- 253k
Only top scored, non community-wiki answers of a minimum length are eligible