University acceptance rates by college and program

InterviewQs

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

How did you like today's question?

15411 Boischatel, Montreal, QC, H9H1Y7, Canada
Don't want to get these emails anymore? We'll forgive you if you unsubscribe

Older messages

Correlation between IMBD and Rotten Tomatoes ratings

Friday, August 5, 2022

Python, Data structures, Correlation Analysis, InterviewQs Correlation between IMBD and Rotten Tomatoes ratings Python Data structures Correlation Analysis Forward this email to a friend or colleague

Package colors

Wednesday, August 3, 2022

Python, Algorithms, Data Structures, Arrays, InterviewQs Package colors Python Algorithms Data Structures Arrays Forward this email to a friend or colleague and challenge them to solve it. Hi, Suppose

Assigned flight seating

Monday, August 1, 2022

Probability Theory, Probability Reasoning, InterviewQs Assigned flight seating Probability Theory Probability Reasoning Forward this email to a friend or colleague and challenge them to solve it. Hi,

Cumulative cash flows

Friday, July 29, 2022

SQL, Database Querying, Window Function, Cumulative Sum, InterviewQs Cumulative cash flows SQL Database Querying Window Function Cumulative Sum Forward this email to a friend or colleague and challenge

Modeling an epidemic

Wednesday, July 27, 2022

Python, Differential equations, Modeling, Stochastic Processes, InterviewQs Modeling an epidemic Python Differential equations Modeling Stochastic Processes Forward this email to a friend or colleague

The image in this post displays its own MD5 hash — and A mysterious voice is haunting American Airlines’ in-flight announcements

Saturday, September 24, 2022

Issue #900 — Top 20 stories of September 25, 2022 Issue #900 — September 25, 2022 You receive this email because you are subscribed to Hacker News Digest. You can open it in the browser if you prefer.

Weekend Reading — 👋 0.1 + 0.2

Saturday, September 24, 2022

This week we play Dungeon & Types, visualize our designs IRL, FOMO into the Metaverse, and wrestle a bear for a Belgium waffle. Weekend Reading — 👋 0.1 + 0.2 By Assaf Arkin – 24 Sep 2022 – View

Daily Coding Problem: Problem #889 [Easy]

Saturday, September 24, 2022

Daily Coding Problem Good morning! Here's your coding interview problem for today. This problem was asked by Amazon. Run-length encoding is a fast and simple method of encoding strings. The basic

Animated | Visualizing 140 Years of Global Surface Temperatures 🌐

Saturday, September 24, 2022

Here's a look at 140 years of global surface temperatures, highlighting the ten coldest and warmest years since 1880. View Online | Subscribe Presented by: Explore what 8 billion people means for

The Best Laptops of 2022

Saturday, September 24, 2022

Did You Know?: The stethoscope was invented in 1816 by French physician René-Théophile-Hyacinthe Laennec and motivated by a desire to accurately (and modestly) diagnose heart problems in women. The

Noonification: Monsters of Mars

Saturday, September 24, 2022

Top Tech Content sent at Noon! Private Applications Begin Here How are you, @hacker? 🪐 What's happening in tech this week: The Noonification by HackerNoon has got you covered with fresh content

Week in Review - GTA 6 footage leaks, Revolut gets hacked, and Wipro fires 300 for "moonlighting"

Saturday, September 24, 2022

TechCrunch Newsletter TechCrunch logo Week in Review logo By Greg Kumparak Saturday, September 24, 2022 Hi, friends! Welcome back to Week in Review, the newsletter where we very quickly sum up the most

London Police Arrested 17-Year-Old Hacker Suspected of Uber and GTA 6 Breaches

Saturday, September 24, 2022

The Hacker News Daily Updates Newsletter cover 2022 First Half Threat Report A Semiannual Report by FortiGuard Labs Download Now Sponsored LATEST NEWS Sep 24, 2022 London Police Arrested 17-Year-Old

Startups Weekly - Tiger Global, fickle checks and the difficulty of acceleration

Saturday, September 24, 2022

TechCrunch Newsletter TechCrunch logo Startups Weekly logo By Natasha Mascarenhas Saturday, September 24, 2022 Welcome to Startups Weekly, a fresh human-first take on this week's startup news and

New Python tutorials on Real Python

Saturday, September 24, 2022

Hey there, There's always something going on over at realpython.com as far as Python tutorials go. Here's what you may have missed this past week: When Do You Use an "..." Ellipsis in