Who Are We?

This post originally appeared on the Software Carpentry website.

For the last three years, I've been storing information about instructors, workshops, and other things in a small SQLite database so that I can look things up and generate statistics when I need to. I can't publish it, since it contains personal identifying information, but since I had to write a script to migrate the data to the tool we're building to manage workshops, it only took another few minutes to create a partly-redacted version of the data. ("Partly" because someone who was really keen could work backward workshop URLs to instructors' names, cross-reference, and recover the names of some fraction of our instructors. Since that information is all public anyway, though, I don't think I've introduced any new risks.)

The SQL source for the database is here; with it, you can regenerate the database using:

$ sqlite3 swc.db < swc-db-2014-12-14.sql

You can then ask lots of questions—some examples are included below. If you'd like a little end-of-year procrastination, what else can you find in this data that's interesting?

-- How many sites have had how many events?  
select count(*), c
from (select count(*) as c
      from site join event
      on site.id=event.site_id
      group by site.id)
group by c
order by c desc;

-- How many people have taught?
select count(distinct person_id)
from person join task join role
on person.id=task.person_id and task.role_id=role.id
where role.name='instructor';

-- How many people started as learners or helpers and became instructors?

-- How often have people taught?
from (select count(*) as c
      from person join task join role
      on person.id=task.person_id and task.role_id=role.id
      where role.name='instructor'
      group by person_id)
group by c
order by c desc;

-- How has each training cohort done?
select cohort.name, count(*), round((100.0 * sum(trainee.complete)) / count(*), 1)
from trainee join cohort
on trainee.cohort_id=cohort.id
where cohort.qualifies
group by cohort_id;