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;
|# instructors||# workshops|
-- 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;
Dialogue & Discussion
You can review our commenting policy here.