University acceptance rates by college and program
Question
Suppose you're given the tables below showing information for various college programs, as well as student applications for those programs. Note that 'college_name_program_id' is a unique identifier for the college/program combination, and 1 indicates a student was accepted in the 'accepted' field in the 'student_apps' table.
Table: college_info
| college_name_program_id | college_name | college_program |
|---|---|---|
| 00001 | University A | Engineering |
| 00002 | University A | Business |
| 00003 | University A | Mathematics |
| 00004 | University B | Engineering |
| 00005 | University B | Business |
| 00006 | University B | Mathematics |
| 00007 | University C | Engineering |
| 00008 | University C | Business |
| 00009 | University C | Mathematics |
Table: student_apps
| student_id | college_name_program_id | accepted |
|---|---|---|
| 31331 | 00001 | 0 |
| 34583 | 00001 | 1 |
| 23455 | 00002 | 0 |
| 23454 | 00005 | 1 |
| 22337 | 00007 | 1 |
| 00112 | 00009 | 0 |
| 12332 | 00004 | 1 |
| 99123 | 00003 | 1 |
| 87903 | 00002 | 0 |
| 23547 | 00002 | 1 |
| 66543 | 00003 | 0 |
| 34554 | 00001 | 1 |
Using the tables above, write a SQL query to return the total number of students accepted as well as the acceptance rate (number accepted over number applied) by each college as well as the total number accepted by each program (agnostic of the college the program is under).
You can view/query this table in an interactive SQL fiddle here.