Q&A for experienced flyers, pilots, and builders of drones and model aircraft
-- UserId: Your user ID number, found in the URL of your profile page on the site: -- TO DO: -- exclude rolled-back edits from contributor list? -- detect locked and migrated questions -- first find all posts by user (plus some other useful keys for joins) create table #PostsByUser ( PostId int primary key, ParentId int, PostTypeId int ) insert into #PostsByUser select Id as PostId, ParentId, PostTypeId from Posts where OwnerUserId = ##UserId:int## ; with PostsByUser as ( select * from #PostsByUser ), -- find last body revision of each post (for Markdown content) LastRevisions as ( select PostsByUser.PostId as PostId, max(PostHistory.Id) as LastRevId from PostsByUser join PostHistory on PostHistory.PostId = PostsByUser.PostId where PostHistory.PostHistoryTypeId in (2,5,8) -- create/edit/rollback body group by PostsByUser.PostId ), -- find all editors for each post Editors as ( select PostHistory.PostId as PostId, PostHistory.UserId as UserId, coalesce(Users.DisplayName, PostHistory.UserDisplayName) as UserName, min(PostHistory.CreationDate) as FirstEditDate from PostsByUser join PostHistory on PostHistory.PostId = PostsByUser.PostId left join Users on Users.Id = PostHistory.UserId where PostHistory.PostHistoryTypeId in (4,5) -- edit body/title and (PostHistory.UserId != ##UserId:int## or PostHistory.UserId is null) group by PostHistory.PostId, PostHistory.UserId, coalesce(Users.DisplayName, PostHistory.UserDisplayName) ), EditorsGrouped as ( select PostId, string_agg(UserName + ' (' + coalesce(cast(UserId as nvarchar), 'n/a') + ')', ', ') within group ( order by FirstEditDate asc ) as EditorNames from Editors group by PostId ), -- find tags for tag wiki posts and excerpts WikiTags as ( select PostId, TagName from PostsByUser join Tags on PostId = ExcerptPostId where PostTypeId = 4 -- tag excerpt union select PostId, TagName from PostsByUser join Tags on PostId = WikiPostId where PostTypeId = 5 -- tag wiki ), -- find duplicate questions Dupes as ( select distinct PostsByUser.PostId from PostsByUser join PostLinks on PostLinks.PostId = PostsByUser.PostId where PostTypeId = 1 and LinkTypeId = 3 -- question, duplicate ) -- now select the actual result data select Posts.Id AS [PostID], N'https://dba.stackexchange.com/q/' + CAST(Parent.Id as nvarchar(max)) + N'/' as [Q_Link], N'https://dba.stackexchange.com/a/' + CAST(Posts.Id as nvarchar(max)) + N'/' as [A_Link], Parent.CreationDate AS Q_date, Posts.CreationDate AS A_date, Parent.OwnerUserId AS AskerId, COALESCE(Parent.OwnerDisplayName, Posts.OwnerDisplayName) AS AskerName, PostTypes.Name as [Type], coalesce(Posts.Title, Parent.Title) as [Title], Parent.Body as [Q_HTML], PostHistory.Text as [Q_Markdown], Posts.Body as [A_HTML], PostHistory.Text as [A_Markdown], coalesce(WikiTags.TagName, Posts.Tags, Parent.Tags) as [Tags], Posts.CreationDate as [Created], Posts.LastEditDate as [Last Edit], EditorNames as [Edited By], Posts.Score as [Score], Posts.FavoriteCount as [Favorites], coalesce(Posts.ViewCount, Parent.ViewCount) as [Views], Posts.AnswerCount as [Answers], iif(Posts.AcceptedAnswerId is not null or Posts.Id = Parent.AcceptedAnswerId, 'Accepted', null) as [Accepted], iif(Posts.CommunityOwnedDate is not null, 'CW', null) as [CW], iif(Posts.ClosedDate is not null, iif(Dupes.PostId is not null, 'Duplicate', 'Closed'), null) as [Closed] from PostsByUser join Posts on Posts.Id = PostsByUser.PostId left join PostTypes on PostTypes.Id = PostsByUser.PostTypeId left join Posts as Parent on Parent.Id = PostsByUser.ParentId left join LastRevisions on LastRevisions.PostId = PostsByUser.PostId left join PostHistory on PostHistory.Id = LastRevisions.LastRevId left join LastRevisions qlr on qlr.PostId = Parent.Id left join PostHistory qph on qph.Id = qlr.LastRevId left join EditorsGrouped on EditorsGrouped.PostId = PostsByUser.PostId left join WikiTags on WikiTags.PostId = PostsByUser.PostId left join Dupes on Dupes.PostId = PostsByUser.PostId