DonorsChoose Gallery

Donors Choose is a great organization that matches donors with classrooms in need. They’re sponsoring the Hacking Education contest (through June 30, 2011) to encourage app developers to find helpful new ways to use their project database.

Dashboards

The Top Subjects dashboard provides a visualization of which subject areas received the most donation dollars. Even simple dashboards like this one provide a greater degree of insight into data than tabular data (let alone raw data output). Using HTSQL and HTRAF, anyone familiar with the data (and who has basic HTML skills) can create data visualizations.

The Subject by State dashboard shows what subject areas and donations are popular for a given state.

The Top Ten Teachers dashboard provides a data driven page that allows a user to select a city and a school, and then displays the top ten teachers for that school and all of their related projects.

Schema Diagram

Query Examples

The demonstration HTSQL server on top of the Donors Choose data set is at http://donorschoose.htsql.org – it is configured to return no more than 1000 rows per request.

Try these queries to get a flavor for what’s in the database:

Top 10 projects

◥/project.limit(10)

This query returns the first 10 rows from the project table — if you omit the limit(10) our demo server silently truncates the results to 1000 rows.

Donations per project

◥/project{essay.title, count(donation)}

This query returns only the title and the number of donations per project.

Projects from LA (CSV)

◥/project{essay.title}?school.city.state='LA'/:csv

This query will return projects where the school’s state is LA (also truncated at 1000 rows). The output format is CSV, replace /:csv with /:json to create your mashups.

Number of teachers, projects, donations

◥/{count(teacher), count(project), count(donation)}

This query returns the number of teachers, cities, and projects that are in the dataset.

Teachers with projects in more than one school

◥/count(teacher?count(project^_schoolid)>1)

This query returns the number of teachers sponsoring projects in more than one school.

Average donation per focus subject

◥/distinct(project{primary_focus_subject}?num_donors)
     {*, sum(^.total_donations)/sum(^.num_donors) -}

This query returns primary focus subjects in descending order by the average donation amount. Unsurprisingly, College & Career Prep takes the cake with $174 per donor.

Cities with more than 5 schools

◥/(city?count(school)>5)
     {city, state, sum(school.project.total_donations)/count(school) -}

This query produce cities with more than 5 schools ordered by the number of donations per school.

La Mesa, CA has by far the highest number of donations per school — $39k. The next closest is E. Palo Alto with $22k.

Accounts by number of donations

◥/account{city{city, state},
         count(donation?dollar_amount='100_and_up') -}

This query produce city and state of each donor ordered by the number of donations of 100 dollars and up.

The Gates Foundation seems to be the single largest donor with 10,298 donations of $100 and up.