SOTorrent sample queries for Google Big Query

Retrieve all post block versions of answer 3758880:

#standardSQL
SELECT *
FROM `sotorrent-org.2018_02_16.PostBlockVersion`
WHERE PostId=3758880
ORDER BY PostHistoryId ASC, LocalId ASC;

Retrieve versions of first code block in which the content was changed:

#standardSQL
SELECT Id, PostHistoryId, LocalId, Content, Length, LineCount, RootPostBlockId, PredSimilarity
FROM `sotorrent-org.2018_02_16.PostBlockVersion`
WHERE RootPostBlockId=140108539
  AND (PredEqual IS NULL OR PredEqual = FALSE)
ORDER BY PostHistoryId ASC;

Analyze which lines were changed in a particular version:

#standardSQL
SELECT PostBlockVersionId, PostBlockDiffOperationId, Text
FROM `sotorrent-org.2018_02_16.PostBlockDiff`
WHERE PostBlockVersionId=140108596;

Retrieve GitHub files that reference answer 3758880:

#standardSQL
SELECT RepoName, Branch, Path, FileExt, Copies, PostId, SOUrl, GHUrl
FROM `sotorrent-org.2018_02_16.PostReferenceGH`
WHERE PostId=3758880;

Retrieve links from the text blocks of the post:

#standardSQL
SELECT *
FROM `sotorrent-org.2018_02_16.PostVersionUrl`
WHERE PostId=3758880;