Graduate Admission Rate
Question
Suppose you have the following tables which contain information about graduate admission rates across different university programs. For the "accepted" field in the "student_applications" table, 0 indicates rejection and 1 indicates accepted.
college_info
| college_program_id | college_id | college_name | program |
|---|---|---|---|
| 00001 | 00001 | University A | Public Health |
| 00002 | 00001 | University A | Engineering |
| 00003 | 00001 | University A | English |
| 00004 | 00002 | University B | Engineering |
| 00005 | 00002 | University B | Mathematics |
| ... | ... | ... | ... |
student_applications
| student_id | student_name | college_program_id | application_date | semester_start | accepted |
|---|---|---|---|---|---|
| 31331 | Beth | 00001 | 2019-07-01 | 2019-09-01 | 0 |
| 31331 | Beth | 00004 | 2019-06-15 | 2019-09-01 | 1 |
| 49760 | Susan | 00003 | 2019-05-30 | 2019-09-01 | 1 |
| 26583 | Jim | 00004 | 2019-06-21 | 2019-09-01 | 0 |
| 20267 | Lin | 00005 | 2019-06-30 | 2019-09-01 | 1 |
| ... | ... | ... | ... | ... | ... |
Using the tables above, write a SQL query that outputs the acceptance rate and total number of applications for each college program on the semester_start date of '2019-09-01'.
Your output should return the columns listed below and each row in the table should represent a single college_program_id.
| college_program_id | acceptance_rate | num_applications |
|---|---|---|
| A | B | C |
| D | E | F |