Askubuntu.com and Loco Teams

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.

About these ads

4 Responses to Askubuntu.com and Loco Teams

  1. arzki says:

    I tweaked your first query a bit so that it orders the users by rank. I can’t find myself saving, so here it is:

    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## + ‘%’) — uppercase location
    order by u.reputation desc

    • Charles Profitt says:

      It was ranked by most recent question so recent questions could be seen first. When searching for people a local team can help I am not sure reputation will come in to play… when looking for potential presenters it could though. Thanks for the tweak.

      • arzki says:

        I am a DB developer and I have made this adjustment out of my habit, but at first glance I couldn’t recognize the database. What is it? Is it microsoft SQL Server? That’s the only DB I haven’t worked on :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 37 other followers

%d bloggers like this: