A Quick Introduction to Data Explorer Queries

Click the Compose Query button up there and start typing your T-SQL query. That's it, it's really that easy! If you're not the query-writing type, you can still make use of all of the community-created queries, or check out the query tutorial for some help on how you can get started.

See something that could be improved with someone else's query? Fork it and make your own edits that they can come back to build on. And using the query editor, you can get easy access to things like the target database schema and your query's execution plan, as well as the ability to download the results as a CSV file.

Query Parameters

Basic Parameters

Editing your queries each time you want to change a parameter value would be inconvenient, so Data Explorer allows you to specify parameter placeholders in your query that will prompt the user for input values. These take the basic form of ##Parameter##, where the parameter name will be used as the label for the input prompt.

Advanced Parameters

Data Explorer parameters can be typed as int, float, or string. Value validation will be performed against user input for typed parameters, and in the case of the string type, the value will be properly escaped and quoted. To type your parameter, declare the type after the variable name using a colon:

##Parameter:type##

You can also specify default values for your parameters as part of the parameter definition by giving the default value after the variable name using a question mark:

##Parameter?value##

By putting both syntaxes together, you can create a typed parameter with a default value:

##Parameter:type?value##

Note that if you use the same parameter in more than one location in the query, you only need to specify the type and default value once.

Parameter Hints

Parameter names aren't always user-friendly, so Data Explorer allows you to provide additional metadata in the form of parameter hints. These can help make the experience more pleasant for people running your queries, and take the form of single line comments in your query. To illustrate this, a query with ##MyParameter## could have a parameter hint like the following:

-- MyParameter: Whatever you want! "You can enter anything"

The label of the prompt for this parameter's value will contain the text "Whatever you want!", with a tooltip stating "You can enter anything". The tooltip portion is optional, so you only need to include it when your label text doesn't capture everything.

Magic Columns and Autolinking

Magic Columns

Data Explorer will create special links to content on the target site when you alias id columns using one of the supported magic column names. For example, the following query will generate a link to ten most recent posts:

SELECT TOP 10 Id AS [Post Link] FROM Posts ORDER BY CreationDate DESC

The available magic columns are:

• [Post Link]
• [User Link]
• [Comment Link]
• [Suggested Edit Link]

In addition to the magic columns listed above, Data Explorer will also try to transform columns named [Tags] and [TagName] into stylized links to tag pages on the target site, like the data-explorer tag on Meta Stack Exchange.

Autolinking

You can create your own links as well, if you return the data in the format Data Explorer expects:

http://example.com|link text

…where the link text is optional. To make it easier to create links pointing currently-queried site, Data Explorer supports the custom scheme site://. You can use this to link to pages not supported by the built-in magic columns, like a particular post's revision history:

SELECT
  'site://posts/' + CAST(Id AS nvarchar) + '/revisions|' + 
  'Revision History for Post ' +  CAST(Id AS nvarchar) AS [Revision Link]
FROM Posts WHERE Id = ##PostId##

Data Explorer also supports the custom scheme query://<id> to link to other queries, enabling you to link directly to drill-downs or related queries from your results.

Graphing

Being able to generate result sets is nice, but sometimes the output is more effectively conveyed as a graph. Data Explorer lets you create simple graphs by automatically plotting results that have one of the following column combinations:

• number, number
• date, number
• number, string, number
• date, string, number

To produce more than one series, additional number columns can be appended to the above combinations. The following query returns results of the first combination:

SELECT Score, COUNT(Score) FROM Posts WHERE Score > 10 AND Score < 100 GROUP BY Score ORDER BY Score

…and produces the following graph of number of posts versus score:

If you'd like to see Data Explorer's graphing support expanded to handle more complex use cases, please create a feature request on Meta Stack Exchange or the GitHub project issues page, or submit a pull request!

Frequently Asked Questions

How often is the Stack Exchange Data Explorer updated?

The data is updated early every Sunday morning around 3:00 UTC. The last update was Jul 20 at 23:59.

What's the point of logging in?

While anonymous users can run queries without any imposed restrictions, there are a few perks to registering:

• A history of queries you've executed for your future reference
• You get credit for the awesome queries you've written
• It proves you aren't part of the robot menace (no more CAPTCHAs!)

I already have a Stack Exchange account, why am I jon.doe#### here?

Data Explorer doesn't participate in the Stack Exchange network authentication scheme, so your account here is completely separate. Don't worry though, you can update your name in your profile at any time!

What runs this thing? Is the Data Explorer powered by hamsters?

No, because hamsters are allergic to SQL. This instance is currently running on SQL Server 2022 and ASP.NET MVC 5.2. For a more in-depth listing of what's under the hood, check out What technologies are used to build Data Explorer?

You didn't cover my question about _______, what's wrong with you?

Well…that's a story for another day. To get help with your Data Explorer question though, or to provide feedback, please ask on Meta Stack Exchange!