Using Data Explorer

Stack Exchange Data Explorer is…

…an open source tool for running arbitrary queries against public data from the Stack Exchange network.
Features include collaborative query editing and OData endpoints for all graduated
and public beta Stack Exchange sites.

The data available here is similar to the data you can find in the Stack Exchange data dumps that are hosted on
the Internet Archive and licensed under
CC BY-SA 3.0. Developers looking to build applications
that run off Stack Exchange data may also want to check out the Stack Exchange API.

Getting Started with 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.

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.

The ##UserId## parameter is treated specially for
users who are logged in. If they have an account on the target site that shares the email used in their Data Explorer account,
their user ID on that site will be automatically populated.

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:

SELECTTOP10IdAS[Post Link]FROMPostsORDERBYCreationDateDESC

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:

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: