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

-- AccountId: Your SE network account ID number, found in the URL of your network profile page:

create table #PostsByUser (
  PostId int primary key,
  ParentId int,
  PostTypeId int
)
create table #Results (
  [Site] nvarchar(128) not null,
  [Post ID] int not null,
  [URL] nvarchar(max),
  [Link] nvarchar(max),
  [Type] nvarchar(50),
  [Title] nvarchar(250),
  [HTML] nvarchar(max),
  [Markdown] nvarchar(max),
  [Tags] nvarchar(250),
  [Created] datetime,
  [Last Edit] datetime,
  [Edited By] nvarchar(max),
  [Score] int,
  [Favorites] int,
  [Views] int,
  [Answers] int,
  [Accepted] nvarchar(20),
  [CW] nvarchar(20),
  [Closed] nvarchar(20)
)

declare @accountid as int = ##AccountId:int##

-- Site details, cf. https://meta.stackexchange.com/a/359795/295232
CREATE TABLE #Sites (URL NVARCHAR(100), SiteName NVARCHAR(100), DatabaseName NVARCHAR(50));
-- fetch Markdown for post containing table with site details
DECLARE @md NVARCHAR(MAX);
SELECT TOP 1 @md = Text
  FROM [StackExchange.Meta].dbo.PostHistory
  WHERE PostId = 359796 AND PostHistoryTypeId = 5
  ORDER BY CreationDate DESC;
-- split Markdown on pipe symbol
CREATE TABLE #TableEntries (ID INT IDENTITY(1, 1), Value NVARCHAR(100));
INSERT INTO #TableEntries (Value)
SELECT LEFT(TRIM(VALUE), 100) FROM STRING_SPLIT(@md, '|')
  WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0;
-- 'pivot' - we have to discard the introductory text, headers and horizontal lines
INSERT INTO #Sites
SELECT t0.Value, t1.Value, t2.Value
  FROM #TableEntries AS t0
  INNER JOIN #TableEntries AS t1
    ON t0.Id + 1 = t1.Id
  INNER JOIN #TableEntries AS t2
    ON t0.Id + 2 = t2.Id
  WHERE t0.Id >= 8 -- that's where the real content starts
    AND t0.Id % 3 = 2;

DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
  AND name LIKE '%dba%'
    AND name NOT LIKE '%_Temp' -- otherwise it won't work while SEDE refreshes

DECLARE @sitedbname AS nvarchar(128)
DECLARE @query AS nvarchar(max)

OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @query = '
    use [' + @sitedbname + '];
    
    declare @localuserid as int = (select Id from Users where AccountId = ' + cast(@accountid as nvarchar(max)) + ');

    insert into #PostsByUser
    select Id as PostId, ParentId, PostTypeId
    from Posts
    where OwnerUserId = @localuserid;

    with
    PostsByUser as (
      select * from #PostsByUser
    ),
    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
    ),
    Editors as (
      select
        PostHistory.PostId as PostId,
        PostHistory.UserId as UserId,
        coalesce(Users.DisplayName, 
          PostHistory.UserDisplayName COLLATE SQL_Latin1_General_CP1_CS_AS ) 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 != @localuserid or PostHistory.UserId is null)
      group by
        PostHistory.PostId,
        PostHistory.UserId,
        coalesce(Users.DisplayName, 
          PostHistory.UserDisplayName COLLATE SQL_Latin1_General_CP1_CS_AS )
    ),
    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
    ),
    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
    ),
    Dupes as (
      select distinct
        PostsByUser.PostId
      from
        PostsByUser
        join PostLinks on PostLinks.PostId = PostsByUser.PostId
      where
        PostTypeId = 1 and LinkTypeId = 3  -- question, duplicate
    )
    insert into #Results
    select
      #Sites.SiteName as [Site],
      Posts.Id as [Post Id],
      concat(#Sites.URL, ''/q/'', Posts.Id) as [URL],
      concat(#Sites.URL, ''/q/'', Posts.Id, ''|'', coalesce(Posts.Title, Parent.Title)) as [Link],
      PostTypes.Name as [Type],
      coalesce(Posts.Title, Parent.Title) as [Title],
      Posts.Body as [HTML],
      PostHistory.Text as [Markdown],
      coalesce(WikiTags.TagName, 
         Posts.Tags COLLATE SQL_Latin1_General_CP1_CS_AS , 
        Parent.Tags COLLATE SQL_Latin1_General_CP1_CS_AS ) 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'', '''') as [Accepted],
      iif(Posts.CommunityOwnedDate is not null, ''CW'', '''') as [CW],
      iif(Posts.ClosedDate is not null, iif(Dupes.PostId is not null, ''Duplicate'', ''Closed''), '''') 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 EditorsGrouped on EditorsGrouped.PostId = PostsByUser.PostId
      left join WikiTags on WikiTags.PostId = PostsByUser.PostId
      left join Dupes on Dupes.PostId = PostsByUser.PostId
      join #Sites on ''' + @sitedbname + ''' = #Sites.DatabaseName;

    delete from #PostsByUser;
  '
  execute(@query)
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

select * from #Results

fork query

edited nov 11

created nov 11 anonymous