Who Are We?

$ 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;