0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programming puzzle enthusiasts and code golfers

/*
In the UserId box below, enter: 
* your user id, to see your stats
* null, to see the stats of all users

*/
declare @UserId int = ##UserId##;

with LanguageNameBoundaries as (
  select 
      p.Id, p.ParentId as QuestionId, p.Body,
      
      case when p.Body like '<h[1-3]><a%'
        then charindex('>', p.Body, 7) + 1
        else 5
      end as LanguageNameBeginIndex,
      
      case when p.Body like '<h[1-3]><a%'
        then charindex('|', 
      replace(replace(replace(replace(p.Body, ' ', '|'), '<', '|'), ':', '|'), ',', '|')
        , charindex('>', p.Body, 7) + 1)
        else charindex('|', 
      replace(replace(replace(replace(p.Body, ' ', '|'), '<', '|'), ':', '|'), ',', '|')
        , 5)
       end as LanguageNameEndIndex
  from
      Posts p
      join Posts q
        on p.ParentId = q.Id
  where 
      p.PostTypeId = 2 --answer
      and p.Body like '<h[1-3]>%'
      and q.Tags not like '%king-of-the-hill%'
      and (p.OwnerUserId = @UserId or @UserId is null)
), AnswerLanguages as (
  select 
    Id, QuestionId, Body, 
    'http://codegolf.stackexchange.com/a/' + convert(varchar(99), Id) as Link,
    lower(substring(Body, LanguageNameBeginIndex, LanguageNameEndIndex-LanguageNameBeginIndex)) as Language,
    LanguageNameBeginIndex,
    LanguageNameEndIndex
  from LanguageNameBoundaries
)
--select * from AnswerLanguages where Language like 'tsql%'
select Language, count(1) as AnswerCount
from AnswerLanguages
group by Language
order by count(1) desc
;

fork query

edited apr 30 15 anonymous

created apr 15 15 w0lf