How do I get City wise users count from Stackoverflow Database?

I found one Location field in Users table which may contains City, State, Country

I wanted to get count all the City wise Users from India. How can I do?

| improve this question | | | | |
  • 5
    You can't. SEDE only offers Location and that field is not standarized nor mandatory. At best you provide the city table of India and then do a fuzzy match on the location field but you better run that on the datadump as SEDE would need more then 2 minutes to give you that info. – rene Sep 26 '19 at 10:12

You can't easily get that data. The location field is optional and free text. Users can enter anything they like in that field. There are no additional fields like city or coutry in the public user profile.

The best you can get is working with your own table of cities in India and then do a fuzzy match on the location field to find the number of users per city.

I've created the following naive proof of concept to demonstrate how that could work.

;with cities as (
  select 'bangalore' city
  union select 'mumbai'
  union select 'new delhi'

select c.city
     , count(*) [# of users]
from users u
cross apply  cities c 
where u.location like '%' + c.city +'%' collate sql_latin1_general_cp1_ci_ai
group by c.city

and here is the result:

list of Cities and user count

It is worth noting that this query probably runs for more then 2 minutes if you would add all cities in India. You better rely on the data dump that you can run on your own hardware without any time/processing restrictions. That also allows you to employ smarter text search algorithms.

| improve this answer | | | | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .