0

Please login or register to vote for this query.

(click on this box to dismiss)

assumed first names from displayname of users active this year and other criteria, setup to extract by page number for export. It's a frikkin mess, don't look. lol Experimenting with PHP "Gender" Extension (https://www.php.net/manual/en/class.gender.php) ... needed some sample data to play with. :-) Total output was about 20 pages (1 million names)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Russian language

select 
case when CharIndex( ' ', displayname )=0 then lower(displayname) else
  Substring( lower(displayname), 1, CharIndex( ' ', displayname ) - 1)
  end as firstname,
  count(*) cnt,
  avg(reputation) rep,
  avg(upvotes) upv,
  avg(downvotes) downv
  into #temp
from users
where LastAccessDate>='2019-01-01' and (not (displayname like '%[0-9]%'))
group by case when CharIndex( ' ', displayname )=0 then lower(displayname) else
  Substring( lower(displayname), 1, CharIndex( ' ', displayname ) - 1)
  end
having charindex('.',case when CharIndex( ' ', displayname )=0 then lower(displayname) else
  Substring( lower(displayname), 1, CharIndex( ' ', displayname ) - 1)
  end)=0 and len(case when CharIndex( ' ', displayname )=0 then lower(displayname) else
  Substring( lower(displayname), 1, CharIndex( ' ', displayname ) - 1)
  end)>1
order by cnt desc

select ROW_NUMBER()OVER( order by cnt desc,firstname) num,* into #out from #temp

declare @pagenum int = 1;

select * from #out
where num between (@pagenum-1)*50000+1 and @pagenum*50000
order by cnt desc , firstname

view current version

edited aug 5 19

created aug 5 19 ashleedawg