SQL Data Science Interview Question Walkthrough | SQL Sundays #6

Hi friends! Welcome back to SQL Sundays! A weekly SQL data science interview questions and answers walkthrough.

This is another data science SQL interview question and answer done from beginning to end in a real interview style.

🔗Affiliates
========================
My SQL for data science interviews course (10 full interviews):

365 Data Science:

Check out StrataScratch for data science interview prep:

📲Socials
========================

🤯Study with Tina
========================
Study with Tina channel:

How to make a studying scoreboard:

Scoreboard website:

livestreaming google calendar:

🎥Other videos you might be interested in
========================
SQL Sundays Playlist:

How I consistently study with a full-time job:

How I would learn to code (if I could start over):

🐈‍⬛🐈‍⬛About me
========================
Hi, my name is Tina and I'm a data scientist at a FAANG company. I was pre-med studying pharmacology at the University of Toronto until I finally accepted that I would make a terrible doctor. I didn't know what to do with myself so I worked for a year as a research assistant for a bioinformatics lab where I learned how to code and became interested in data science. I then did a masters in computer science (MCIT) at the University of Pennsylvania before ending up at my current job in tech :)

📧Contact
========================
youtube: youtube comments are by far the best way to get a response from me!

========================
Some links are affiliate links and I may receive a small portion of the sales price at no cost to you. I really appreciate your support in helping improve this channel! :)

[wilsonman]

My solution was:

SELECT title, budget/num_emp AS budget_to_employee_ratio
FROM ms_projects MP
JOIN (SELECT project_id, COUNT(*) AS num_emp
FROM ms_emp_projects
GROUP BY project_id) EC
ON MP.id = EC.project_id
ORDER BY 2 DESC
LIMIT 5

It looks a little clunkier but I think this would be more optimized because it's joining to a smaller table, instead of the whole ms_emp_projects table. I didn't test query speed or anything though so I could definitely be wrong! :D

[sanketsharma]

Hi Tina,

How is SQLZOO for practice?

[kevinl]

GROUP BY can/should exclude budget, no? Why include it?

[qizhou]

Is it OK to use JOIN instead of INNER JOIN? I'm not 100% sure about the reason.

[larigiba]

Just in time!


😁
My solution (slightly more convoluted than yours :D) would be this:

SELECT budget/n_of_employees AS total_cost, title
FROM ms_projects
JOIN(
SELECT COUNT(emp_id) AS n_of_employees, project_id
FROM ms_emp_projects
GROUP BY project_id
) AS employees_per_project
ON = ms_projects.id
GROUP BY title, budget, n_of_employees
ORDER BY total_cost DESC;

[shobhamourya]

Why do you group by budget also?

SELECT title as project, (budget)/COUNT(emp_id) as budget_emp_ratio
FROM ms_projects p
JOIN ms_emp_projects e
ON p.id = e.project_id
GROUP BY title
HAVING COUNT(emp_id) > 0
ORDER BY budget_emp_ratio DESC
LIMIT 5