Return to Revisions

The magic column [User Link] is extremely expensive or at least that is what Sql Server thinks so it often gives up.

I've made a small change by replacing the magic column with the autolinking feature and this does work:

select
      'site://users/' 
    + cast(OwnerUserId as nvarchar) 
    + '|'
    + (select displayname from users du where du.id = owneruserid)  as [User],
    sum(case when up > 0 and down = 0 then 1 else 0 end) as socratism
from (
  select
    OwnerUserId,
    max(case when score > 0 then 1 else 0 end) as up,
    max(case when score < 1 then 1 else 0 end) as down
  from Posts
  where PostTypeId = 1 -- a question
  group by CONVERT(VARCHAR(10), creationDate, 111), OwnerUserId
) question_days
group by question_days.OwnerUserId
order by socratism desc;

The site:// construct expands to the base url of the site, in this case http://stackoverflow.com the rest is straight forward SQL string concatenarion and inline sql statements

    'site://users/'  --  'http://stackoverflow.com/users/' (1)
    + cast(OwnerUserId as nvarchar) -- '578411'
    + '|' -- the pipe symbol '|'
    + (select displayname  -- project displayname
       from users du       -- found in the users table, aliased as du     
       where du.id = owneruserid) -- the row where its id equals the owneruserid

end result:

'http://stackoverflow.com/users/578411|rene' and that string is rendered as an proper link, rene, not as plain text.

^{1. Technically the site:// isn't expanded server side but client side. For the understanding of how this works is an implementation detail not relevant for this discussion}