University acceptance rates by college and program

University acceptance rates by college and program

SQL Database Querying Aggregation Functions

Forward this email to a friend or colleague and challenge them to solve it.

Hi,

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.

You’ve accumulated 329 questions since joining our email list. Create a premium account to access detailed solutions to each problem (including this one) + our new Case studies.

Was this email forwarded to you? You can join our list here to receive questions