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 changechange by replacing the magic columnmagic 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 concatenation and inline SQL statements
'site://users/' -- 'https://stackoverflow.com/users/' (1)
+ cast(OwnerUserId as nvarchar) -- '578411'
+ '|' -- the pipe symbol '|'
+ (select displayname -- project the display name
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:
'https://stackoverflow.com/users/578411|rene' and that string is rendered as a 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 it is an implementation detail not relevant for this discussion
