During UDS-P there was a discussion of trying to make information from askubuntu.com useful to loco teams looking to meet the needs of Ubuntu users in their area. Tonight I spoke with Marco Ceppi and worked to refine a data query that will pull some useful data for loco teams.
The query we started with tonight was:
select top 1000 u.id as [User ID], u.displayname as [User Name], u.reputation as [User Rep] from users u where upper(u.location) like upper('%' + ##YourLocation:string## + '%')
This query will return the top 1000 users in your area. If you want to run it click here.
The next query, tweaked with Marco’s assistance, was this one:
-- find questions form your area select Users.reputation, Users.Id as [User Link], Posts.Id as [Post Link], Posts.score from posts LEFT JOIN Users on Users.id=posts.OwnerUserId where PostTypeId = 1 AND upper(location) like upper('%' + ##YourLocation:string## + '%') -- uppercase location -- AND AnswerCount = 0 -- AND ClosedDate is null ORDER BY Posts.CreationDate DESC
This one will return a list of questions asked in your area ordered by the newest ones first. This query can be run by clicking here. This query can be of great use to loco teams by making them aware of questions that are being asked in their area. The team can then schedule presentations on the subject or include how-to information on their mailing list.
The real goal is to enable people making use of askubuntu.com to find their local community team and vice-versa. I hope to work with Marco more in this cycle on making the connection between askubuntu.com and loco communities a two way avenue.