0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

-- 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 [Question Link],
  N'https://dba.stackexchange.com/a/' + CAST(Posts.Id as nvarchar(max))  + N'/'   as [Answer Link],
  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

fork query

edited nov 11

created nov 11 anonymous