[Magic Link] usage in comments by users

1

Please login or register to vote for this query.

(click on this box to dismiss)

last 30 days (or as specified), sorted by # of magic links used. Full list: https://meta.stackexchange.com/a/242306/370758

Stack Overflow

Q&A for professional and enthusiast programmers

set nocount on

select 
  userid, 
  lower(text) as text
into #comments from comments 
where  
  creationdate >= dateadd(d,0-##DaysBack:int?30##,getdate())
  and userid is not null 
  
select
  UserId,
  count(*) as [Total #Comments],  --(these have to be "escaped" with '!' ' since [] is for Like Search:)
  sum(case when text like '%![mcve]%'            escape '!' then 1 else 0 end) as "[mcve]",
  sum(case when text like '%![[help/on-topic]]%' escape '!' then 1 else 0 end) as "[help/on-topic]", 
  sum(case when text like '%![edit]%'            escape '!' then 1 else 0 end) as "[edit]",
  sum(case when text like '%![ask]%'             escape '!' then 1 else 0 end) as "[ask]",
  sum(case when text like '%![tour]%'            escape '!' then 1 else 0 end) as "[tour]",
  sum(case when text like '%![help]%'            escape '!' then 1 else 0 end) as "[help]",
  sum(case when text like '%![answer]%'          escape '!' then 1 else 0 end) as "[answer]",
  sum(case when text like '%![so]%'              escape '!' then 1 else 0 end) as "[so]",
  sum(case when text like '%![su]%'              escape '!' then 1 else 0 end) as "[su]",
  sum(case when text like '%![sf]%'              escape '!' then 1 else 0 end) as "[sf]",
  sum(case when text like '%![meta]%'            escape '!' then 1 else 0 end) as "[meta]",
  sum(case when text like '%![main]%'            escape '!' then 1 else 0 end) as "[main]",
  sum(case when text like '%![chat]%'            escape '!' then 1 else 0 end) as "[chat]",
  sum(case when text like '%![help/dont-ask]%'   escape '!' then 1 else 0 end) as "[help/dont-ask]",
  sum(case when text like '%![se]%'              escape '!' then 1 else 0 end) as "[se]",
  sum(case when text like '%![help/behavior]%'   escape '!' then 1 else 0 end) as "[help/behavior]",
  sum(case when text like '%![meta-help]%'       escape '!' then 1 else 0 end) as "[meta-help]",
  sum(case when text like '%![metase]%'          escape '!' then 1 else 0 end) as "[metase]",
  sum(case when text like '%![a51]%'             escape '!' then 1 else 0 end) as "[a51]"
into #temp from #comments
group by userid

select
   "[help/on-topic]"+"[mcve]"+"[ask]"+"[answer]"+"[meta]"+"[chat]"+"[help]"+"[help/dont-ask]"+"[help/behavior]"
   +"[meta-help]"+"[main]"+"[edit]"+"[tour]"+"[so]"+"[su]"+"[sf]"+"[metase]"+"[a51]"+"[se]" as [Magic Links Used], *
into #summ from #temp 

select 
  userid as [User Link],
  * 
from #summ 
where [Magic Links Used] > 0
order by [Magic Links Used] desc

--DaysBack:How many days back to include in report?

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results