Can I get Correct SE attribution from Data Explorer

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/questions/315534/can-i-get-correct-se-attribution-from-data-explorer/315540?noredirect=1#comment1034120_315540

Stack Overflow

Q&A for professional and enthusiast programmers

select top 10 -- remove TOP 10 if you want up to 50,000 rows
       Title
     , Posts.Id [Post Id]
       -- add together the bits and pieces to one string
     , concat(N'site://questions/', Posts.Id,N'|', Title) as [Question Link]
     , concat(N'//stackoverflow.com', N'/questions/', Posts.Id,N'|', Title) as [Raw Question Link]
     , DisplayName
     , Users.Id [User Id]
     , concat(N'site://users/', Users.Id,N'|', DisplayName) as [Author Link]
     , concat(N'//stackoverflow.com', N'/users/', Users.Id,N'|', DisplayName) as [Raw Author Link]
from Posts 
INNER JOIN Users ON Posts.OwnerUserId = Users.id
where Posts.PostTypeId = 1 -- Questions


select db_name()


SELECT 
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', db_name()) > 0,
    IIF(PATINDEX('%.Meta', db_name()) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', db_name()) > 0,
      IIF(PATINDEX('%.Meta', db_name()) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', db_name()) > 0,
          CASE SUBSTRING(db_name(), 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          ELSE SUBSTRING(db_name(), 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', db_name()) > 0,
            CASE SUBSTRING(db_name(), 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(db_name(), 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', db_name()) > 0,
              'meta.' + SUBSTRING(db_name(), 0, PATINDEX('%.Meta', db_name())) + '.com',
              db_name() + '.com'
            )
          )
        )
      )
      ))

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results