# Data explorer challenge: Generate list of members by location
**URL:** https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [12 januari 2017 om 15:11 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648 "2017-01-12T15:11:45Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [12 januari 2017 om 15:11 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648/1 "2017-01-12T15:11:45Z")
On our site, we populate the location field via SSO with the country where the member is based. I had a request this week from a moderator who wanted to alert members in Pakistan about a new resource published specific to Pakistan. He wanted to do this by mentioning all of them by name in a post.
I looked into it and found that I don’t know an easy way to do this via the UI. Location isn’t even provided in user exports which surprised me. Am I missing something?
[Data explorer](https://meta.discourse.org/t/32566?silent=true) seems the low hanging fruit home for solving this problem. **Has anyone created a [data explorer](https://meta.discourse.org/t/32566?silent=true) query to search the user list by location and spit out a list of usernames who share that location?**
A better solution for the longer term to this specific problem might be a new feature to be able to mention users by country somehow, e.g. `!pakistan` or some such.
---
### Author:  [@mikechristopher](https://meta.discourse.org/u/mikechristopher)
#### Post date: [12 januari 2017 om 15:36 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648/2 "2017-01-12T15:36:51Z")
The issue with the location field as it is is that is free text entry - so whilst some people could have put in Pakistan others could have just put in India for example. I would love to be able to use a localised mention system or at least some visibility on where members are from but whilst its free text and not validated or from a drop down its going to prove difficult.
---
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [12 januari 2017 om 15:40 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648/3 "2017-01-12T15:40:51Z")
Thanks for the thoughts. On my site it’s not a freetext field, actually - it’s handled via SSO externally and is always countries. I’m sure many sites do this. In any case this query doesn’t have to look for a perfect match.
Maybe another approach would be to use the user list, which currently only allows filtering by username. An additional `filter by profile` option would be interesting - e.g. show me everyone who indicates Pakistan in their profile bio or location. And then an admin link to export directly from there. 😉
All of this would be moot if profile details were included in user export - let me check again to see if I have it right that location is not actually exported. That really surprised me.
---
### Author:  [@gpetrov](https://meta.discourse.org/u/gpetrov)
#### Post date: [26 november 2018 om 18:40 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648/4 "2018-11-26T18:40:24Z")
Did you get the country filter working? I would be very interested in seeing it. My members really like to group per country
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [24 september 2021 om 22:13 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648/5 "2021-09-24T22:13:44Z")
---
### Author:  [@DonH](https://meta.discourse.org/u/DonH)
#### Post date: [17 juli 2023 om 00:17 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648/6 "2023-07-17T00:17:37Z")
Maybe I don’t understand the question but, if I do, then
```sql
SELECT u.username
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE location = 'Pakistan'
```
or, equally,
```sql
SELECT username from user_profiles, users
where user_id = id and location = 'Pakistan'
```
Note that the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) does not accept double quotes as delimiters.
---
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [19 juli 2023 om 18:00 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648/7 "2023-07-19T18:00:50Z")
Nice! Thanks for the (very belated) offer of a query to solve my problem. Maybe it will be helpful to others. I no longer work at Namati where we were trying to target a post to members in Pakistan. The way we solved this type of problem in the end was by creating an insight dashboard in WordPress, which we had set up to accompany Discourse using the [WP Discourse](https://github.com/discourse/wp-discourse) wordpress plugin and SSO. In the dashboard we created a view allowing us to filter by location and many other criteria besides.
We could then export that list to a spreadsheet and grab the usernames or email addresses to use in various ways in Discourse. My preferred way was to send a PM to each user directly to let them know about an opportunity specific to them.
---
### Author:  [@ToddZ](https://meta.discourse.org/u/ToddZ)
#### Post date: [10 oktober 2025 om 19:53 UTC](https://meta.discourse.org/t/data-explorer-challenge-generate-list-of-members-by-location/55648/8 "2025-10-10T19:53:37Z")
I had an occasion to expand Don’s query a bit to produce a list of users, locations, & emails for all users with a location entered. Might be interesting or useful in some cases:
```sql
SELECT u.username, u.locale, up.location, ue.email
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id
LEFT JOIN user_emails ue ON u.id = ue.user_id
-- comment out WHERE clause for complete user list
WHERE up.location <> ''
ORDER BY up.location
```