Stack Overflow
Stack Overflow

Questions tagged [dataexplorer]

The Data Explorer (SEDE), located at data.stackexchange.com, allows you to create and run queries against copies of the databases of popular Stack Exchange sites to investigate trends or satisfy your curiosity.

Definition:

The Stack Exchange Data Explorer (SEDE) allows you to create and run queries against copies of the databases of popular Stack Exchange sites to investigate trends or satisfy your curiosity.

The Data Explorer uses a read-only data dump from a subset of Stack Exchange websites, updated once every few months, and includes posts, users, badges, tags, suggested edits and anonymized votes.

The Stack Exchange Data Explorer itself is Open Source software. If you would like to help make it more awesome, check out the code.

Important Links:

84 questions
5
votes
4 answers

T-SQL pattern matching with exceptions

Here's a problem I've repeatedly encountered while playing with the Stack Exchange Data Explorer, which is based on T-SQL: How to search for a string except when it occurs as a substring of some other string? For example, how can I select all…
Ilmari Karonen
  • 49,047
  • 9
  • 93
  • 153
4
votes
1 answer

How to use the Stack Exchange Data Explorer to find every user's top tags?

I use this query in the Stack Exchange Data Explorer: select id, reputation from users where reputation >300000 What should I add to this query in order to have an additional column with the "best" tag of the user? That is the tag where the user…
Pozmanski
  • 181
  • 11
3
votes
2 answers

Where does Stack Overflow store GitHub profile links?

I was going through Stack Overflow db structure on https://data.stackexchange.com/stackoverflow/queries and wondering where the user's GitHub link is. I can see the website URL listed against user in schema but there is no field referencing GitHub…
Waqar Ul Khaf
  • 569
  • 1
  • 3
  • 15
3
votes
1 answer

SQL on Stack Exchange Data Explorer - defining values

I'm new to Stack Overflow and am a very novice coder. I'm using this tutorial to learn how to write queries to search the Stack Exchange Data Explorer. I'm looking at this line of code: SELECT p.Title, p.Id, p.Score, a.Score AS "Accepted Score",…
Sara Roberts
  • 33
  • 5
3
votes
1 answer

Query for fastest growing tags in data explorer?

I'd like to compare the popularity of tags between two months, ordered by the biggest change. I've worked out how to count the number of tags in a month, but not how to compare them. Here's what I have so far: select TOP 10 tags.tagname,…
13ren
  • 11,887
  • 9
  • 47
  • 64
3
votes
1 answer

What do I need to change to get a count of deleted, locked posts from SEDE?

I'm trying to query for some stats on how many posts have been deleted as spam/abusive. I've got what I think should work, but the numbers it throws out don't make sense. Since posts deleted this way are characterized by being both deleted and…
ArtOfCode
  • 5,702
  • 5
  • 37
  • 56
3
votes
4 answers

How I can replace odd patterns inside a string?

I'm in the process of creating a temporary procedure in SQL because I have a value of a table which is written in markdown, so it appear as rendered HTML in the web browser (markdown to HTML conversion). String of the column currently look like…
user2284570
  • 2,891
  • 3
  • 26
  • 74
3
votes
0 answers

How does data.stackexchange.com allow queries securely?

https://data.stackexchange.com/ lets me query some (all?) of stackexchange's data/tables using arbitrary SQL queries, including parametrization. What program do they use to do this and is it published? I want to create something like this myself…
Barry Carter
  • 456
  • 1
  • 4
  • 9
3
votes
1 answer

SQL Server: Dynamic columns returned from query

I've got a fairly complex SQL query running against data.stackexchange.com. The query can be found at this link (and pasted below). DECLARE @Location varchar(128) = ##Location:string## DECLARE @RepLimit int = ##RepLimit:int## SELECT…
Mike Trpcic
  • 25,305
  • 8
  • 78
  • 114
3
votes
1 answer

Multiple full index scans being performed when using a conditional join statement

I've got a problem querying a table which has rows with a parent/child relationship. When working up a simplified example, I realized that the stackexchange schema is very similar. So imagine I'm querying the stackoverflow posts table via…
John
  • 1,502
  • 2
  • 13
  • 40
3
votes
1 answer

SQL query - invalid column name and parameter - data.SE

Not a programmer but using data.stackexchange.com. I'm querying the public SE data explorer and the column names that I'm specifying are invalid. Also, how do I specify Count (Posts) and Count (Votes)? Is anyone who queried data.se and knows the db…
Sirius Black
  • 33
  • 4
3
votes
1 answer

SQL: Why can't I set a variable to the result of a query?

I'm trying to run this query on the SO Data Explorer: DECLARE @totalRep float SET @totalRep = SELECT SUM(Users.Reputation) FROM Users And I keep getting Incorrect syntax near the keyword 'SELECT'. What am I doing wrong?
Yossale
  • 14,165
  • 22
  • 82
  • 109
2
votes
1 answer

get query tree/hierarchy using Python sqlparse

I'm working on a customer database which has lots of views created from complex queries with lots of suqueries e and joins, as the example query below. I'm already using sqlparse to get table names, but now I'm wondering if its possible to get a…
Marcos Scalabrin
  • 71
  • 1
  • 7
2
votes
2 answers

Get "rank" of badge on SO for my user - query is slow - speedup possible?

I was curious the how many people got the before me - I am able to get this information python 2019-01-02 09:09:15 Gold 454 with this (slow running) query: (I was unable to single/cross login with my main user on data explorer, hence the…
Patrick Artner
  • 50,409
  • 9
  • 43
  • 69
2
votes
5 answers

SQL Find Reciprocal Relationship

I am trying to find a situation using the Stack Exchange Data Explorer (SEDE) where two distinct users on Stack Overflow have accepted an answer from each other. So for example: Post A { Id: 1, OwnerUserId: "user1", AcceptedAnswerId: "user2"…
Andrew Allison
  • 1,122
  • 2
  • 13
  • 30
1
2 3 4 5 6