Three Tricky Analytics Interview Questions with Andrew

Today I'm doing a mock data science interview with one of our coaches, Andrew, from DataLeap. We tackle three analytics interview questions by solving them with SQL. Each one is progressively harder and Andrew explains his methodology towards solving each question!
Want to be featured in the next mock interview video? Apply here: airtable.com/shrdQrwKK7xxGLm6l
👉 Subscribe to my data science channel: bit.ly/2xYkyUM
Use the code "datasciencejay" and get 10% off data science interview prep 🔥 : www.interviewquery.com/pricin...
❓ Check out our data science course: www.interviewquery.com/course...
🔑 Get professional coaching here: www.interviewquery.com/coachi...
🐦 Follow us on Twitter: / interview_query
Check out Andrew's channel here: / @andrewmomoney
Quick Links:
0:33 - Andrew's Introduction
2:35- SQL Interview Question 1
9:42 - SQL Interview Question 2
19:44 - SQL Interview Question 3
More from Jay:
Read my personal blog: datastream.substack.com/
Follow me on Linkedin: / jay-feng-ab66b049
Find me on Twitter: / datasciencejay

[@Vintagetube310]

Man this was so dope. In a data analytics student and I’m so happy that a lot of this resonated with me as I’m still learning. Everyone who has or is studying coding and programming, understands that it’s a steep learning curve and a lot of times you might find yourself in a rut thinking you’re never going to get the material. But when you observe mock interviews and the info they are covering definitely resonates with you, it reinforces your progress knowing you’re heading in the right direction. Everyone who is learning how to program, KEE GOING! thanks fellas’

[@Auslims]

I really like andrew’s thought process. It gives me so much insights to prepare for the coming interviews!

[@shao4686]

I really liked the thought process sharing. Thanks.

[@abhijitbhaduriya117]

These are super helpful. Thanks for this, Jay.

[@KisaanTuber]

Thanks Jay for this awesome session. I have few sugestions regarding Part 1. We should emphasize on using RANK()/DENSE_RANK() function instead of ORDER BY and LIMIT. RANK()/DENSE_RANK() will take care of scenarios when multiple employees have same salary. Let me know your thoughts.

[@dontstopn0w]

Really appreciate this video. Extremely helpful!

[@Mario-ox5dm]

This mock interview definitely was helpful! Thank you Jay & Andrew!

[@venkatnetha8382]

For a 1200 long pages of question bank on real world scenarios to make you think like a data scientist. please visit: payhip.com/b/ndY6 You can download the sample pages so as to see the quality of the content.

[@ShowTiimee123]

Happy to see that what I'm learning in my classes right now is actually applicable to the real world!

[@dbc3745]

Thank you for this. Please give us more glimpses of real world data analytics/scientist work and how they solve problems through data

[@D3Jia]

andrew is such articulate and intelligent dude.

[@quishzhu]

i am sooo lucky to pump into this video before the data science tech interview tomorrow!

[@shrutisinghal8556]

In second question, Andrew assumed that Employees who have not finished any project should be considered while Jay assumed that any employee who has even one unfinished project must be considered. None of them made clear their assumption and therefore both had such different queries. I like how Andrew thought! Good interview session.

[@danielxing1034]

Fantastic mock! Very thorough thinking paths! But wonder if we really need to go through this entire path during the interview? Because the number of problems solved is also an important metric.

[@hardikojha220]

Very interesting thought process

[@leos5246]

During the second task with this complex construction, I literally thought "Why it's so complex? Why you cannot use just where clause" and it's surprising that Jay is added this solution after :). The only one thing about performance. If the tables are huge you don't want to use "NOT IN" construction, it's preferable to use NOT EXISTS instead, much more effective but mostly the same code.

[@arindampatra6283]

Not as tricky as I expected , but I enjoyed how the guy thinks

[@AnujJaiswal91]

Great process

[@iqjayfeng]

Thanks for watching! If you're interested in learning more about SQL, check out our SQL course. The course is designed to help you learn everything you need to know about working with data, from basic concepts to more advanced techniques. www.interviewquery.com/learning-paths/sql

[@mypure]

Thanks a ton. I will do honest attempt to solve the problems and learn.

[@arjunekbote8979]

Steps: 1. Gets project from boss to find employees who haven't finished project 2. Remembers that he hasn't completed any project till now 3. Does not complete project

[@khiemgluong]

Were not creating a table of employees we need to "furlough" or anything

[@gabiscarabel]

Excellent video!

[@yyyj926]

Thank you, Andrew and Jay.

[@venkatnetha8382]

For a 1200 long pages of question bank on real world scenarios to make you think like a data scientist. please visit: payhip.com/b/ndY6 You can download the sample pages so as to see the quality of the content.

[@cinyenne]

This sort of discussion helps us to understand your thought process better

[@AndrewMoMoney]

Cheers, Cy :)

[@venkatnetha8382]

For a 1200 long pages of question bank on real world scenarios to make you think like a data scientist. please visit: payhip.com/b/ndY6 You can download the sample pages so as to see the quality of the content.

[@iremozen7172]

great video!! thank you.

[@tz1938]

This is awesomeeee!

[@nickayala7263]

First Question: I think it would be better to COUNT(DISTINCT Project_id) and have a WHERE End_dt IS NOT NULL. We don't know if this is a unique column or not. Could a project have been closed and then re-opened? Would that have the same ID or a new ID? Counting just end dates could lead us to incorrect counts.

[@user-be7bz6kt1k]

Regarding the 2nd question: If an employee has 3 projects on hand and none of them are finished, wouldn't her salary be summed up 3 times according to the query in this video?

[@rakeshpillai2173]

Yes. Also not sure how this works in MySQL, but in both postgres and sql server, grouping by id will give you separate rows of salaries and won't add them up.

[@nppreaches]

which query of the two will perform better assuming you have a big number in terms of employees and projects?

[@Rohit_Ankita]

For part 1: can we do count(distinct project_id) >= 10 in having clause and where clause should have end date is not null. Thoughts.

[@khiemgluong]

learning SQL and this is very helpful

[@akshitsinghal8590]

Really productive session

[@shashankrai10]

For the second question can we do this: SELECT sum(Sum_Of_Salary) as Total_sum FROM ( select sum(e.salary) as Sum_Of_Salary, Count(end_dt) as num_projects FROM employees as e RIGHT JOIN projects as p ON e.id = p.employee_id GROUP BY(A.id) ) WHERE num_projects = 0 The right join takes only the values from projects that have an id in employees. So that takes care of the third definition - employees who have no project started are not counted. Among those employees who have no end_dt, their count of end_dt should appear as 0. And then outside of brackets, we simply sum the salaries where number of projects are 0.

[@Hotobu]

...Please don't use right joins, there's never a good reason to use a right join.

[@5lanediver]

good call -- they never addressed that third case

[@5lanediver]

@@Hotobu then just rewrite it as a left join -

[@GH-hg6vs]

I was having this question - For the second part, why not just selecting the employees that don't have a valid "end_date". In this case, no matter how many project he/she has been assigned to, he/she didn't finish any of them. Then I saw Jay's solution. Perfect.

[@briceinsingapore]

For question 2, does the following query work as well? select sum(salary) from( select distinct id, salary from employees e inner join projects p on e.id=p.employee_id where end_dt is null or end_dt>current_date()) as cte

[@Han-ve8uh]

Strange to name this cte at the end when no WITH is used. You mean subquery? This query will include employees who have also finished some projects. The question wanted people who have started 1 or many projects but never finished a single one

[@omerbraude5694]

In the first problem, how can he order by e.salary without having it in the group by clause? Wouldn't it throw an error? I would imagine it must be 'order by avg(salary)' or 'max(salary)' which would yield the same result. Alternatively, you could add the salary column to the group by clause. Am I right?

[@0yustas0]

you're right

[@shifaali1923]

how about? select r.id from (select e.id as id,,e.salary,p.project_id,rank() over(Partition by e.id,p.projectid order by e.salary asc) as rank from employ e inner join project p on e.id=p.project_id where p.end_dt is not null)r where r.rank

[@MattIn3rtia]

Not sure about that first one. The end date might be in the future so the projects unfinished. The Qs a bit ambigous. I'd have gone with the following: SELECT id FROM employees WHERE id IN (SELECT DISTINCT employee_id FROM projects WHERE COUNT(DISTINCT project_id_) > =10) ORDER BY salary ASC LIMIT 5

[@AbhishekKumar-sy7dn]

Hey Jay. I think the query by Andrew has got an issue. He has used Group By e.id and then taken sum(salary). This would output salary for each employee and NOT a SUM of All employees salary. Correct me, if otherwise Thanx for the good work Cheers

[@santiagocanon5281]

I have an interview with a large tech company on Monday for a DS position. While I am confident in my SQL skills, it is good to see and understand the soft skills that should be leveraged during the interview. I did want to ask is my position also requires R which I am proficient in as well, but do you have any insight into R interview questions?

[@venkatnetha8382]

For a 1200 long pages of question bank on real world scenarios to make you think like a data scientist. please visit: payhip.com/b/ndY6 You can download the sample pages so as to see the quality of the content.

[@mimansamaheshwari4664]

In the first question if employee table why did we drop cases with null end_date?

[@antonisdaniil2857]

I believe this is better so we if we have employee with same salary we get all of them with cte as (select e.id, Dense_RANK() over(order by salary) as r from projects t join employees e on e.id=t.employee_id group by t.employee_id having count(end_dt)>10) select * from cte where r=5

[@averw3047]

My solution is similar to yours, except "Where r

[@GalaxyZhou1006]

for part 1 the solution does not seem to include case for example that the sixth lowest paid employee has the same salary with the fifth one, do you include the sixth person or not. in that case you could apply rank or dense_rank?

[@sunny73555]

You should use dense rank

[@rick2591]

The join would break the query causing salaries double triple, etc.

[@OkechukwuOfili]

What if different projects finished on the same date: [ count (p.end_dt) >= 10 ] won't your count then be off? Would it not be better to do a [ count (distinct p.projects_id) >= 10 ] ?

[@hariniprabakaran5434]

Yes, also We need to handle the end date case

[@pavelbakunovitch]

No, it won't. count(p.end_dt) is counting every not null p.end_dt even they are the same.

[@brothermalcolm]

You don’t need a cte if you place the case statement inside the having statement sum(case when...)

[@NK-ju6ns]

infact do we need case statement? why not this - having count(e.id) = count(p.end_dt is null)

[@brittosabu8401]

Can we use this for second question ? SELECT SUM(salary) FROM Employees WHERE employee_id IN (SELECT employee_id FROM Projects GROUP BY 1 HAVING MAX(END_DATE) IS NULL)

[@mattcarter4749]

I believe the only error is using "employee_id" in your where statement. Since you have "From Employees", you would need "Where id IN".

[@mattcarter4749]

This is the query I wrote to solve, very similar to yours except I used "having count(end_dt) = 0" instead of max() select sum(salary) from Employees where id in (select employee_id from Projects group by employee_id having count(end_dt) = 0 )

[@ananyadutta7852]

For the first ques what if two projects have the same end date we would end up counting it as 1 project which could be actually multiple projects. I propose to filer as end date not null and count project id. Am i missing something here?

[@Amber-ws5hy]

Andrew used count() instead of count(distinct), so it shouldn't matter I think

[@5lanediver]

for problem 2 you never tacked case 3, when there is no projectid associated with an employeeid...

[@tilibanner5920]

Question 2: sum(e.salary) and then group by employee_id? That's not producing what the question is asking for.

[@joehatch3289]

Can I ask, for part 1 what ensures that there is no duplicates? If the lowest salary emplyee was on multiple projects?

[@Han-ve8uh]

group by e.id removes possibility of any duplicates in employee/salary that can cause some of lowest 5 salaries to come from same employee

[@itsadamjosh]

Whats the code for amc and gme to go to the moon

[@Peekabostreet]

They miscommunicated a couple times but very helpful

[@brothermalcolm]

Employees who’ve done at least 10 project, then select bottom 5 by salary

[@ravitanwar9537]

oo princes of data science :)

[@AndrewMoMoney]

Wahahaha our royalty status is highly in question

[@ravitanwar9537]

@@AndrewMoMoney 😂 I can vouch for that

[@max007sh]

Was wondering why you used aggregating (and having clause) for the first question. Wouldn't be simpler to use a where clause?

[@nanfengbb]

Nope. "WHERE COUNT(p.*)>=10 " would show an syntax error. It has to be GROUP BY followed by HAVING.

[@max007sh]

@@nanfengbb @B Karcher select e.id from employee e inner join (select employee_id eid, count(end_dt) c from project group by employee_id) temp on e.id=temp.eid where temp.eid>10 order by ...

[@max007sh]

but now that I think about it, having seems simpler 😅

[@SamairaJain11]

In the first question he is literally passing the time🤣

[@flyingcucumber]

Got a interview tomorrow. I'm f*cked

[@christopherroan2023]

lmao I hope it went well! How did it go?

[@masheTyMasha]

Cool

[@pinkyellow1able]

can you please do benford's law with the 2020 election numbers? would really get your channel some views and open up the truth to alot of people

[@comatosetorpor3602]

i dont think they r tricky at all. for any one who knows and uses sql in industry, they r basic.

[@annxiao7721]

I can’t talk and think at the same time. I hate it that straightforward SQL case can appear so much more difficult just because u have to explain to the interviewers while u r working on it.. it’s just anti humanity…. 😢

[@drkenny7928]

Dude made this overly complicated

[@jonathancauchi6457]

Cool video, but not tricky at all.

[@tsume184tg]

SELECT e.id, e.salary, p.employee_id, p.project_id, COUNT(project_id) AS numberofprojects FROM projects AS p INNER JOIN employees AS e ON e.id = p.employee_id WHERE numberofprojects >= 10 GROUP BY e.id, e.salary, p.employee_id, p.project_id ORDER BY e.salary DESC LIMIT 5; I think that should work for the first scenario. I am still learning, on day 2. Hopefully i got that correct. If i can get some feedback that would be great.

[@1danielsz24]

I am completely started in sql but I imagined a different way and I would like to know if I would return the output correctly SELECT employee_id, MIN(salary), COUNT(project_id) AS project_number FROM employee e INNER JOIN projects p ON e.id = p.employee_id WHERE project_number >= 10 GROUP BY employee_id ORDER BY salary LIMIT = 5