Free Trader Beowulf

Supporting Free and Open Source Software

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.

4 comments on “Askubuntu.com and Loco Teams

  1. arzki
    January 17, 2012

    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
      January 17, 2012

      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
        January 17, 2012

        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:)

        • Charles Profitt
          January 17, 2012

          I will be honest I am not sure what they use for their DB engine.

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

Information

This entry was posted on January 16, 2012 by in Linux, Ubuntu.
Follow

Get every new post delivered to your Inbox.

Join 43 other followers

%d bloggers like this: