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?
Meta Stack Exchange is a question and answer site for meta-discussion of the Stack Exchange family of Q&A websites. It only takes a minute to sign up.Sign up to join this community
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:
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.