Three Tricky Analytics Interview Questions with Andrew
VloĆŸit
- Äas pĆidĂĄn 2. 11. 2020
- 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 - VÄda a technologie
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â
I really like andrewâs thought process. It gives me so much insights to prepare for the coming interviews!
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.
I really liked the thought process sharing. Thanks.
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.
These are super helpful. Thanks for this, Jay.
Really appreciate this video. Extremely helpful!
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
Were not creating a table of employees we need to "furlough" or anything
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
Thanks a ton. I will do honest attempt to solve the problems and learn.
andrew is such articulate and intelligent dude.
Happy to see that what I'm learning in my classes right now is actually applicable to the real world!
Not as tricky as I expected , but I enjoyed how the guy thinks
This mock interview definitely was helpful! Thank you Jay & Andrew!
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.
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?
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.
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.
Very interesting thought process
i am sooo lucky to pump into this video before the data science tech interview tomorrow!
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.
...Please don't use right joins, there's never a good reason to use a right join.
good call -- they never addressed that third case
@@Hotobu then just rewrite it as a left join -
Thank you for this. Please give us more glimpses of real world data analytics/scientist work and how they solve problems through data
great video!! thank you.
Great process
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.
Excellent video!
Thank you, Andrew and Jay.
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.
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.
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.
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?
you're right
This is awesomeeee!
Really productive session
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
My solution is similar to yours, except "Where r
learning SQL and this is very helpful
This sort of discussion helps us to understand your thought process better
Cheers, Cy :)
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.
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
The join would break the query causing salaries double triple, etc.
which query of the two will perform better assuming you have a big number in terms of employees and projects?
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
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
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.
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?
You should use dense rank
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
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?
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.
In the first question if employee table why did we drop cases with null end_date?
Just out if curiosity, a weird question related to 3rd scenerio. As it is mentioned in the video that person who completed 10 projects in a week shouldn't be given more raise than a person who completes 5 project in a month.
Ideally person completing 10 projects in leas time is more efficient and should be given more raise?
Please suggest where I am wrong
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
Question 2: sum(e.salary) and then group by employee_id? That's not producing what the question is asking for.
You donât need a cte if you place the case statement inside the having statement sum(case when...)
infact do we need case statement? why not this - having count(e.id) = count(p.end_dt is null)
oo princes of data science :)
Wahahaha our royalty status is highly in question
@@AndrewMoMoney đ
I can vouch for that
for problem 2 you never tacked case 3, when there is no projectid associated with an employeeid...
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)
I believe the only error is using "employee_id" in your where statement. Since you have "From Employees", you would need "Where id IN".
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
)
Can I ask, for part 1 what ensures that there is no duplicates? If the lowest salary emplyee was on multiple projects?
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
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 ] ?
Yes, also We need to handle the end date case
No, it won't. count(p.end_dt) is counting every not null p.end_dt even they are the same.
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?
Andrew used count() instead of count(distinct), so it shouldn't matter I think
They miscommunicated a couple times but very helpful
In the first question he is literally passing the timeđ€Ł
Whats the code for amc and gme to go to the moon
Employees whoâve done at least 10 project, then select bottom 5 by salary
Was wondering why you used aggregating (and having clause) for the first question. Wouldn't be simpler to use a where clause?
Nope. "WHERE COUNT(p.*)>=10 " would show an syntax error. It has to be GROUP BY followed by HAVING.
@@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 ...
but now that I think about it, having seems simpler đ
Got a interview tomorrow. I'm f*cked
lmao I hope it went well! How did it go?
Cool
i dont think they r tricky at all. for any one who knows and uses sql in industry, they r basic.
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
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âŠ. đą
Cool video, but not tricky at all.
Dude made this overly complicated
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.
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