[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

Gardening and Landscaping

Q&A for gardeners and landscapers

select --top 5000
  UserId,
  count(*) as [Total #Comments],
  sum(case when text like '%![[help/on-topic]]%' escape '!' then 1 else 0 end) as "[help/on-topic]", 
  sum(case when text like '%![mcve]%'            escape '!' then 1 else 0 end) as "[mcve]",
  sum(case when text like '%![ask]%'             escape '!' then 1 else 0 end) as "[ask]",
  sum(case when text like '%![answer]%'          escape '!' then 1 else 0 end) as "[answer]",
  sum(case when text like '%![meta]%'            escape '!' then 1 else 0 end) as "[meta]",
  sum(case when text like '%![chat]%'            escape '!' then 1 else 0 end) as "[chat]",
  sum(case when text like '%![help]%'            escape '!' then 1 else 0 end) as "[help]",
  sum(case when text like '%![help/dont-ask]%'   escape '!' then 1 else 0 end) as "[help/dont-ask]",
  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 '%![main]%'            escape '!' then 1 else 0 end) as "[main]",
  sum(case when text like '%![edit]%'            escape '!' then 1 else 0 end) as "[edit]",
  sum(case when text like '%![tour]%'            escape '!' then 1 else 0 end) as "[tour]",
  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 '%![metase]%'          escape '!' then 1 else 0 end) as "[metase]",
  sum(case when text like '%![a51]%'             escape '!' then 1 else 0 end) as "[a51]",
  sum(case when text like '%![se]%'              escape '!' then 1 else 0 end) as "[se]"
  --(had to be "escaped" with '!' ' since [] is for Like Search)
into #temp from comments
where  
  creationdate >= dateadd(d,-60,getdate()) ----<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  and userid is not null 
group by userid
order by count(*) desc

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 userid in (##UserID##)
order by [Magic Links Used] desc

/*select top 500
userid as [User Link], format(1.0*[Magic Links Used]/[Total #Comments],'0.00') as [Magic Links per Comment],
rank() over (order by 1.0*[Magic Links Used]/[Total #Comments] desc)
from #summ
order by 1.0*[Magic Links Used]/[Total #Comments] desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results