What SEDE examples would be most useful to new users?

Question

This question asks about an SEDE tutorial, which doesn't really exist. The answer there suggests that you can look at existing queries, which I think is how most SEDE users have learned, but that has a couple challenges:

• There are a lot of queries out there.

• Some of them might not be good examples (expensive, unnecessarily cumbersome, obscurely clever, fragile).

So it would be nice to have a small number of good examples, ones that are models we want people to emulate and that are comprehensible to SEDE beginners. This would be a nice supplement to the schema documentation.

So my question: if we wanted to pick, or write, a small number (half a dozen?) of good solid examples to use in an SEDE tutorial, which ones should they be? Please propose (or specify) candidates or, better yet, sets of candidates that together would teach people to get the data they want and not tear out too much hair or pound SE's servers mercilessly.

Answer 1

Basically I would go with the tools to be able to do stuff yourself first (how to see what the contents of each field in a table are, how to figure out stuff like 'PostTypes' that are numbers), then doing simple WHERE statements (find all questions in the past month), then a simple JOIN (users and posts, for instance)

1. How to determine the contents of a table
2. How to determine the meaning of numerical fields
3. Simple WHERE statements on a single table
4. JOIN statement between two tables
5. Data.SE specific quirks ([Post Link], ##Variable##)
6. Possibly more complex stuff (sub-queries, CASE clauses, IIF clauses)

Start with the basics

Explain the general layout -- title field, description field, query field, and the table list to the right. Then teach how to run a simple query to look at what's in one of the tables:

-- SELECT * grabs everything FROM the specified table
-- Since that's a lot of info, we limit it to 100 entries
SELECT TOP 100 * 
FROM Posts
-- This tells you what fields are in the table with sample data

Teach about numerical fields

-- Run this query, what does PostTypeId mean? It's a number!
SELECT Top 100 Title, Id, PostTypeId
FROM Posts
ORDER BY Id DESC
-- Now comment the above lines with --
-- And uncomment the lines below and run again
-- SELECT DISTINCT p.PostTypeId, pt.Name
-- FROM Posts p
-- JOIN PostTypes pt ON p.PostTypeId = pt.Id
-- ORDER BY p.PostTypeId ASC

-- This can be seen much easier if you click on the ? icon
-- next to the PostTypes table in the right hand table list

Simple WHERE statements

-- What if we only want to see certain posts?
-- For instance, what about all posts in July 2014?
SELECT Title, Id, PostTypeId
FROM Posts
WHERE CreationDate BETWEEN '2014-07-01' AND '2014-07-31'
-- But some posts have no titles? Why is that? Check PostTypeId
-- Not all items have all data types -- answers don't have titles
-- Uncomment the next line to filter the data to only show questions
--   AND PostTypeId = 1

JOIN Statements

-- But what if we want to look only at questions in july by users with <500 rep?
-- We can use a JOIN clause to look at data from two tables
-- But we have to tell the query how to link the tables
SELECT p.Title, p.Id, u.DisplayName, u.Reputation
-- We are using prefixes to explain which table to take info from
FROM Posts p 
-- We are naming Posts 'p' so if two fields have the same name we can differentiate
JOIN Users u ON p.OwnerUserId = u.Id
-- We want to give the info for the user who owns the post on the same line
WHERE p.CreationDate BETWEEN '2014-07-01' AND '2014-07-31'
  AND p.PostTypeId = 1
-- If we changed this to 'u.CreationDate' what do you think happens?
-- Try it and find out!

Data.SE Specific Quirks

Not writing examples for these, but teach about [Post Link], [User Link], ##Variables## and ##UserId## at the very least.

More complex stuff?

Conditional clauses (CASE, IIF), aggregate clauses (SUM, COUNT), sub-queries (WITH tableA AS), etc. can also be covered for more complex queries, but the above probably handles 70% of most basic queries.