Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save KarthiAru/a5b19bd094103bc0fe38410f038e8cfe to your computer and use it in GitHub Desktop.
Save KarthiAru/a5b19bd094103bc0fe38410f038e8cfe to your computer and use it in GitHub Desktop.
Stack Exchange Data Explorer - For graph analysis you'd need the source, target and weight i.e. Asker -> Answerer or vice versa. This query will return the same for a specified tag, date range and answer score. You can use 'Score' as the weight.
DECLARE @tag0 nvarchar(25) = ##tag0:string?r##
DECLARE @start datetime = ##start:string?2014-01-01##
DECLARE @end datetime = ##end:string?2014-12-31##
DECLARE @minscore int = ##minscore?10##
SELECT a.Id AS [Post Link], u.DisplayName AS "Asker", au.DisplayName AS "Answerer",
a.Score, p.CreationDate AS "Q_Date", a.CreationDate AS "A_Date"
FROM Tags t
JOIN PostTags pt ON pt.TagId = t.Id
JOIN Posts p ON p.Id = pt.PostId
JOIN Posts a ON a.ParentId = p.Id
JOIN Users u ON p.OwnerUserId = u.Id
JOIN Users au ON a.OwnerUserId = au.Id
WHERE TagName = @tag0
AND a.PostTypeId = 2
AND a.CreationDate < @end AND a.CreationDate > @start
AND a.Score >= @minscore
ORDER BY a.Score DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment