A Gentle Introduction To the Stack Overflow Schema

Last Updated 6 years ago

Stack Overflow Database, Stack Overflow Public Data Dump

Stack: Dumped

Wanna learn the schema of the free Stack Overflow public database? If you want the Full Nelson, it’s all documented here. That’s a great resource if you’re looking to learn more, but generally when you’re looking at a data dump and building up demo queries, you wanna get an idea of where the data skews, and how the tables relate.

What took me forever to break out of was always using the Id column in the Users table — it’s the PK/CX, and it’s just too easy for the optimizer to make good query plans from that. To help you get the optimizer making some bad choices, here are some other ways to think of the table relationships.

This isn’t exhaustive, it’s just for the four main tables that we use when writing demo queries. (Note: I’m using > to denote relationships, not that one side is greater than the other. They’re both great. They both get a gold star on the fridge.)

Badges

Badges.UserId > Users.Id
Badges.UserId > Posts.OwnerUserId
Badges.UserId > Comments.UserId
Badges.UserId > Votes.UserId (but this is largely anonymized)

Badges.UserId > Users.Id

Badges.UserId > Posts.OwnerUserId

Badges.UserId > Comments.UserId

Badges.UserId > Votes.UserId (but this is largely anonymized)

Comments

Comments.UserId > Users.Id
Comments.UserId > Comments.UserId
Comments.UserId > Votes.UserId (but this is largely anonymized)
Comments.PostId > Posts.Id
Comments.PostId > Votes.PostId

Comments.UserId > Users.Id

Comments.UserId > Comments.UserId

Comments.UserId > Votes.UserId (but this is largely anonymized)

Comments.PostId > Posts.Id

Comments.PostId > Votes.PostId

Users

Users.Id > Posts.OwnerUserId
Users.Id > Comments.UserId
Users.Id > Badges.UserId
Users.Id > Votes.UserId (but this is largely anonymized)

Users.Id > Posts.OwnerUserId

Users.Id > Comments.UserId

Users.Id > Badges.UserId

Users.Id > Votes.UserId (but this is largely anonymized)

Posts

Posts.Id > Comments.PostId
Posts.Id > Votes.PostId
Posts.OwnerUserId > Users.Id
Posts.OwnerUserId > Badges.UserId
Posts.OwnerUserId > Comments.UserId
Posts.OwnerUserId > Votes.UserId (but this is largely anonymized)

Posts.Id > Comments.PostId

Posts.Id > Votes.PostId

Posts.OwnerUserId > Users.Id

Posts.OwnerUserId > Badges.UserId

Posts.OwnerUserId > Comments.UserId

Posts.OwnerUserId > Votes.UserId (but this is largely anonymized)

Votes

Votes.UserId > Users.Id
Votes.UserId > Badges.UserId
Votes.UserId > Comments.UserId
Votes.UserId > Posts.OwnerUserId
Votes.PostId > Comments.PostId

Votes.UserId > Users.Id

Votes.UserId > Badges.UserId

Votes.UserId > Comments.UserId

Votes.UserId > Posts.OwnerUserId

Votes.PostId > Comments.PostId

Toolin’ Around

To get an idea about where interesting data distributions live, I generally use these queries. Feel free to change or remove the TOP N parts according to your needs.

All the dates are stored as DATETIME, so I tend to flatten them to DATEs. You can go further and group them by year, but it generally trends smallest to largest from 2008 to current. If the data dump you’re working with is from a partial year (like 2016-03), then the current year will obviously have less in it.

Badges

/*Badges*/
SELECT  TOP 10 *
FROM    dbo.Badges;

/*count by user id*/
SELECT UserId, COUNT(*) AS records
FROM dbo.Badges AS b
GROUP BY b.UserId
ORDER BY records DESC

/*count by date*/
SELECT CONVERT(DATE, [Date]) AS [Date], COUNT(*) AS records
FROM dbo.Badges AS b
GROUP BY CONVERT(DATE, [Date])
ORDER BY records DESC

/*count by badge name*/
SELECT b.Name, COUNT(*) AS records
FROM dbo.Badges AS b
GROUP BY b.Name
ORDER BY records DESC

/*Badges*/

SELECT TOP 10 *

FROM dbo.Badges;

/*count by user id*/

SELECT UserId, COUNT(*) AS records

FROM dbo.Badges AS b

GROUP BY b.UserId

ORDER BY records DESC

/*count by date*/

SELECT CONVERT(DATE, [Date]) AS [Date], COUNT(*) AS records

FROM dbo.Badges AS b

GROUP BY CONVERT(DATE, [Date])

ORDER BY records DESC

/*count by badge name*/

SELECT b.Name, COUNT(*) AS records

FROM dbo.Badges AS b

GROUP BY b.Name

ORDER BY records DESC

Comments

/*Comments*/
SELECT  TOP 10 *
FROM    dbo.Comments;

/*count by date*/
SELECT CONVERT(DATE, c.CreationDate) AS CreationDate, COUNT(*) AS records
FROM dbo.Comments AS c
GROUP BY CONVERT(DATE, c.CreationDate)
ORDER BY records DESC

/*count by post id*/
SELECT c.PostId, COUNT(*) AS records
FROM dbo.Comments AS c
GROUP BY c.PostId
ORDER BY records DESC

/*count by user id*/
SELECT c.UserId, COUNT(*) AS records
FROM dbo.Comments AS c
GROUP BY c.UserId
ORDER BY records DESC

/*top 1000 high scores*/
SELECT TOP 1000 c.Score
FROM dbo.Comments AS c
ORDER BY c.Score DESC

/*Comments*/

SELECT TOP 10 *

FROM dbo.Comments;

/*count by date*/

SELECT CONVERT(DATE, c.CreationDate) AS CreationDate, COUNT(*) AS records

FROM dbo.Comments AS c

GROUP BY CONVERT(DATE, c.CreationDate)

ORDER BY records DESC

/*count by post id*/

SELECT c.PostId, COUNT(*) AS records

FROM dbo.Comments AS c

GROUP BY c.PostId

ORDER BY records DESC

/*count by user id*/

SELECT c.UserId, COUNT(*) AS records

FROM dbo.Comments AS c

GROUP BY c.UserId

ORDER BY records DESC

/*top 1000 high scores*/

SELECT TOP 1000 c.Score

FROM dbo.Comments AS c

ORDER BY c.Score DESC

Users

/*Users*/
SELECT  TOP 10 *
FROM    dbo.Users;

/*Top 10 ages*/
SELECT TOP 10 Age, COUNT(*) AS records
FROM dbo.Users AS u
GROUP BY u.Age
ORDER BY records DESC

/*count by creation date*/
SELECT CONVERT(DATE, u.CreationDate) AS CreationDate, COUNT(*) AS records
FROM dbo.Users AS u
GROUP BY CONVERT(DATE, u.CreationDate)
ORDER BY records DESC

/*top 10 reputation count*/
SELECT TOP 10 u.Reputation, COUNT(*) AS records
FROM dbo.Users AS u
GROUP BY u.Reputation
ORDER BY records DESC

/*count by last access date*/
SELECT CONVERT(DATE, u.LastAccessDate) AS CreationDate, COUNT(*) AS records
FROM dbo.Users AS u
GROUP BY CONVERT(DATE, u.LastAccessDate)
ORDER BY records DESC

/*Users*/

SELECT TOP 10 *

FROM dbo.Users;

/*Top 10 ages*/

SELECT TOP 10 Age, COUNT(*) AS records

FROM dbo.Users AS u

GROUP BY u.Age

ORDER BY records DESC

/*count by creation date*/

SELECT CONVERT(DATE, u.CreationDate) AS CreationDate, COUNT(*) AS records

FROM dbo.Users AS u

GROUP BY CONVERT(DATE, u.CreationDate)

ORDER BY records DESC

/*top 10 reputation count*/

SELECT TOP 10 u.Reputation, COUNT(*) AS records

FROM dbo.Users AS u

GROUP BY u.Reputation

ORDER BY records DESC

/*count by last access date*/

SELECT CONVERT(DATE, u.LastAccessDate) AS CreationDate, COUNT(*) AS records

FROM dbo.Users AS u

GROUP BY CONVERT(DATE, u.LastAccessDate)

ORDER BY records DESC

Votes

/*Votes*/
SELECT   v.PostId, COUNT(*) AS records
FROM     dbo.Votes AS v
GROUP BY v.PostId
ORDER BY records DESC;

SELECT COUNT(*) AS records
FROM   dbo.Votes AS v
WHERE  v.UserId IS NULL;

SELECT COUNT(*) AS records
FROM   dbo.Votes AS v
WHERE  v.UserId IS NOT NULL;

SELECT   v.PostId, v.BountyAmount
FROM     dbo.Votes AS v
WHERE    v.BountyAmount IS NOT NULL
GROUP BY v.PostId, v.BountyAmount
ORDER BY v.BountyAmount DESC;

SELECT   v.UserId, v.BountyAmount, COUNT(*) AS records
FROM     dbo.Votes AS v
WHERE    v.BountyAmount IS NOT NULL
AND		 v.UserId IS NOT NULL
GROUP BY v.UserId, v.BountyAmount
ORDER BY records DESC;

SELECT CONVERT(DATE, v.CreationDate) AS CreationDate, COUNT(*) AS records
FROM dbo.Votes AS v
GROUP BY CONVERT(DATE, v.CreationDate)
ORDER BY records DESC;

/*Votes*/

SELECT v.PostId, COUNT(*) AS records

FROM dbo.Votes AS v

GROUP BY v.PostId

ORDER BY records DESC;

SELECT COUNT(*) AS records

FROM dbo.Votes AS v

WHERE v.UserId IS NULL;

SELECT COUNT(*) AS records

FROM dbo.Votes AS v

WHERE v.UserId IS NOT NULL;

SELECT v.PostId, v.BountyAmount

FROM dbo.Votes AS v

WHERE v.BountyAmount IS NOT NULL

GROUP BY v.PostId, v.BountyAmount

ORDER BY v.BountyAmount DESC;

SELECT v.UserId, v.BountyAmount, COUNT(*) AS records

FROM dbo.Votes AS v

WHERE v.BountyAmount IS NOT NULL

AND v.UserId IS NOT NULL

GROUP BY v.UserId, v.BountyAmount

ORDER BY records DESC;

SELECT CONVERT(DATE, v.CreationDate) AS CreationDate, COUNT(*) AS records

FROM dbo.Votes AS v

GROUP BY CONVERT(DATE, v.CreationDate)

ORDER BY records DESC;

Posts

/*Posts*/
SELECT  TOP 10 *
FROM    dbo.Posts;

/*questions with accepted answers, without accepted answers, plus total count*/
SELECT  SUM(CASE WHEN p.AcceptedAnswerId = 0
                 THEN 1
            END) AS NoAnswer,
        SUM(CASE WHEN p.AcceptedAnswerId > 0
                 THEN 1
            END) AS YesAnswer,
        COUNT(*) AS records
FROM    dbo.Posts AS p;

/*top 100 answer counts*/
SELECT TOP 100 p.AnswerCount
FROM dbo.Posts AS p
ORDER BY p.AnswerCount DESC

/*count of closed dates*/
SELECT CONVERT(DATE, p.ClosedDate) AS ClosedDate, COUNT(*) AS records
FROM dbo.Posts AS p
GROUP BY CONVERT(DATE, p.ClosedDate)
ORDER BY records DESC

/*top 100 comment counts*/
SELECT TOP 100 CommentCount
FROM dbo.Posts AS p
ORDER BY p.CommentCount DESC

/*count of community owned dates*/
SELECT CONVERT(DATE, p.CommunityOwnedDate) AS ClosedDate, COUNT(*) AS records
FROM dbo.Posts AS p
GROUP BY CONVERT(DATE, p.CommunityOwnedDate)
ORDER BY records DESC

/*count of creation dates*/
SELECT CONVERT(DATE, p.CreationDate) AS ClosedDate, COUNT(*) AS records
FROM dbo.Posts AS p
GROUP BY CONVERT(DATE, p.CreationDate)
ORDER BY records DESC

/*top 100 posts by favorite count*/
SELECT TOP 100 p.FavoriteCount
FROM dbo.Posts AS p
ORDER BY p.FavoriteCount DESC

/*count of last activity dates*/
SELECT CONVERT(DATE, p.LastActivityDate) AS ClosedDate, COUNT(*) AS records
FROM dbo.Posts AS p
GROUP BY CONVERT(DATE, p.LastActivityDate)
ORDER BY records DESC

/*count of last edit dates*/
SELECT CONVERT(DATE, p.LastEditDate) AS ClosedDate, COUNT(*) AS records
FROM dbo.Posts AS p
GROUP BY CONVERT(DATE, p.LastEditDate)
ORDER BY records DESC

/*count of last editor ids (there's also a last editor displayname column)*/
SELECT TOP 100 p.LastEditorUserId, COUNT(*) AS records
FROM dbo.Posts AS p
GROUP BY p.LastEditorUserId
ORDER BY records DESC

/*count by owner user id*/
SELECT OwnerUserId, COUNT(*) AS records
FROM dbo.Posts AS p
GROUP BY p.OwnerUserId
ORDER BY records DESC

/*count by post type*/
SELECT p.PostTypeId, COUNT(*) AS records
FROM dbo.Posts AS p
GROUP BY p.PostTypeId
ORDER BY records DESC

/*top 100 posts by score*/
SELECT TOP 100 Score
FROM dbo.Posts AS p
ORDER BY p.Score DESC

/*Posts*/

SELECT TOP 10 *

FROM dbo.Posts;

/*questions with accepted answers, without accepted answers, plus total count*/

SELECT SUM(CASE WHEN p.AcceptedAnswerId = 0

THEN 1

END) AS NoAnswer,

SUM(CASE WHEN p.AcceptedAnswerId > 0

THEN 1

END) AS YesAnswer,

COUNT(*) AS records

FROM dbo.Posts AS p;

/*top 100 answer counts*/

SELECT TOP 100 p.AnswerCount

FROM dbo.Posts AS p

ORDER BY p.AnswerCount DESC

/*count of closed dates*/

SELECT CONVERT(DATE, p.ClosedDate) AS ClosedDate, COUNT(*) AS records

FROM dbo.Posts AS p

GROUP BY CONVERT(DATE, p.ClosedDate)

ORDER BY records DESC

/*top 100 comment counts*/

SELECT TOP 100 CommentCount

FROM dbo.Posts AS p

ORDER BY p.CommentCount DESC

/*count of community owned dates*/

SELECT CONVERT(DATE, p.CommunityOwnedDate) AS ClosedDate, COUNT(*) AS records

FROM dbo.Posts AS p

GROUP BY CONVERT(DATE, p.CommunityOwnedDate)

ORDER BY records DESC

/*count of creation dates*/

SELECT CONVERT(DATE, p.CreationDate) AS ClosedDate, COUNT(*) AS records

FROM dbo.Posts AS p

GROUP BY CONVERT(DATE, p.CreationDate)

ORDER BY records DESC

/*top 100 posts by favorite count*/

SELECT TOP 100 p.FavoriteCount

FROM dbo.Posts AS p

ORDER BY p.FavoriteCount DESC

/*count of last activity dates*/

SELECT CONVERT(DATE, p.LastActivityDate) AS ClosedDate, COUNT(*) AS records

FROM dbo.Posts AS p

GROUP BY CONVERT(DATE, p.LastActivityDate)

ORDER BY records DESC

/*count of last edit dates*/

SELECT CONVERT(DATE, p.LastEditDate) AS ClosedDate, COUNT(*) AS records

FROM dbo.Posts AS p

GROUP BY CONVERT(DATE, p.LastEditDate)

ORDER BY records DESC

/*count of last editor ids (there's also a last editor displayname column)*/

SELECT TOP 100 p.LastEditorUserId, COUNT(*) AS records

FROM dbo.Posts AS p

GROUP BY p.LastEditorUserId

ORDER BY records DESC

/*count by owner user id*/

SELECT OwnerUserId, COUNT(*) AS records

FROM dbo.Posts AS p

GROUP BY p.OwnerUserId

ORDER BY records DESC

/*count by post type*/

SELECT p.PostTypeId, COUNT(*) AS records

FROM dbo.Posts AS p

GROUP BY p.PostTypeId

ORDER BY records DESC

/*top 100 posts by score*/

SELECT TOP 100 Score

FROM dbo.Posts AS p

ORDER BY p.Score DESC

Anyway, I hope you find these useful!

Thanks for reading!

Brent says: if you’re still stumbling along with that crappy ol’ AdventureWorks because the Stack database is too big, good news! We’ve released a new StackOverflow2010 database that’s only a 1GB download, no torrenting required, and extracts to a comfy 10GB database with data from the first few years of Stack Overflow’s existence, 2008-2010. That’s also kinda nice as a presenter because you don’t have to worry about the data changing and breaking your demos.

Functions Can Still Use Indexes, Kinda.

[Video] Office Hours 2018/1/31 (With Transcriptions)

11 Comments. Leave new

DoubleBarrellDarrell
February 2, 2018 1:06 pm

You get a gold star!
And you get a gold star!
Erik gets a gold star!
Everyone gets a gold star!
Thank You, Erik, for, uh, un-thinking of these queries a user would write to blow up our performance.

Reply
- Erik Darling
  February 2, 2018 1:07 pm
  
  Apparently domain knowledge is important? Who knew…
  
  Reply
- DoubleBarrellDarrell
  February 2, 2018 1:07 pm
  
  Sorry. Relationships. We can work on some bad queries.
  
  Reply
  - DoubleBarrellDarrell
    February 2, 2018 1:13 pm
    
    I’m confused.
    Is the sql presented performance badness or just the relationships that we can build, as listed?
    Thank You
    
    Reply
    - Erik Darling
      February 2, 2018 1:16 pm
      
      I tried to outline usable relationships between the tables (that’s the part at the top), and then give you some queries to explore the data distributions in different columns you may want to query when writing demos. The performance of those is neither here nor there 😉
      
      Reply
      - DoubleBarrellDarrell
        February 2, 2018 3:17 pm
        
        Got it. Thank You, Erik. You’re awesome to all of us.
      - Erik Darling
        February 2, 2018 4:16 pm
        
        Thanks, Darrell. Tonight I’ll be drinking to remember ?
muneeb
May 8, 2018 9:51 am

hi, thanks for sharing this useful resources.
I need to confirm about LastActivityDate, is this field refers to latest answer/comment posted against a question?

Reply
- Erik Darling
  May 8, 2018 9:58 am
  
  Muneeb — follow the documentation link. It has the answer.
  
  Reply
Umer Qaiser
September 22, 2019 1:57 am

Hey can you please answer this question relating to SEDE ?

https://stackoverflow.com/questions/58046151/get-all-posts-with-self-accepted-answers-in-sede-stack-exchange-data-explorer/58047428#58047428

Reply
- Brent Ozar
  September 22, 2019 2:47 am
  
  You already have a correct answer on the question. Glad it worked out for you! Take care.
  
  Reply