Can I get Correct SE attribution from Data Explorer

Question

I'd like to get titles (only) from various SE sites for my writer's prompts website.

I cannot see how to use SE Data Explorer queries to get the required information.

From the Attribution page I see the following requirements:

1: Visually indicate that the content is from Stack Overflow or the Stack Exchange network in some way. It doesn’t have to be obnoxious; a discreet text blurb is fine.

2: Hyperlink directly to the original question on the source site (e.g., http://stackoverflow.com/questions/12345)

3: Show the author names for every question and answer

4: Hyperlink each author name directly back to their user profile page on the source site (e.g., http://stackoverflow.com/users/12345/username)

However I cannot see many of these fields in the Data Explorer Query Builder.

If I run this query for WorldBuilding

select Title, DisplayName, WebsiteUrl
from Posts 
INNER JOIN Users ON Posts.OwnerUserId = Users.id
where Posts.Title IS NOT NULL

Problems:

1: I get empty rows or external websites for websiteUrl - which I'm guessing will not be correct attribution for criteria 4

2: I cannot get the "original question" url criteria 2

3: No user profile Url criteria 4

4: I only need titles, not question body or answers - do I need to show "every author for answers" criteria 3

Is there any way to use DE to get correct attribution?

Answer 1

You have to concat the various bits and pieces together yourself and if you only want questions, filter on PostTypeid. The rows with PostTypeid = 1 are the questions.

Putting this in a a query will give you this:

    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

Notice that the site name is hardcoded here in the Raw fields. In the Link fields I used the link expansion that works in the Result grid but it won't in CSV output.

Notice the results you will get in Excel:

However, Glorfindel created an expression to translate the database name into an URL so you can output correct URL's directly from the server instead of relying on client-side processing:

  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 'Health' THEN 'medicalsciences'
          WHEN 'Health.Meta' THEN 'medicalsciences.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'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.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'
            )
          )
        )
      )
      ))

It would go as first entry in the concat statement for the Raw Links. Doing so would also give you this output in a CSV file. I leave that as an exercise for the reader.