3

Please login or register to vote for this query.

(click on this box to dismiss)

Produces a ranked count of questions migrated away from this site. Enter the number of years back to query in the prompt.

Q&A for ebook publishers and readers

select
  rank() over(order by count(id) desc) [Rank],
  substring(comment,charindex('//',comment)+2,charindex('.com',comment)-charindex('//',comment)+2) [SentTo],
  count(id) [##NumYears:int?3## Year Count]

from
  posthistory
  
where
  posthistorytypeid = 35  --migrated to another site
  and creationdate >= dateadd(dd,-365*##NumYears:int?3##,getdate()) --within the last year
  
group by 
  substring(comment,charindex('//',comment)+2,charindex('.com',comment)-charindex('//',comment)+2)
  
order by
  count(id) desc

--The following line sets up the prompt and it's tooltip:
--NumYears: How many years back?"How many years into the past would you like to query for migrated questions?"

fork query

edited apr 23 18

created apr 23 18 ashleedawg