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’
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.
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.
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.
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
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.
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?
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
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.
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
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.
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
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.
andrew pretty much messed up the entire second query. There was no need to over-complicate it. select sum(salary) from employees where id IN ( select distinct employeed_id from projects where end_dt is null )
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
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?
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
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
They both did wrong on the second question, they agreed on the definition that employees without projects on hand are not considered but Jay's answer included those employees.
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)
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 )
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.
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
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 ] ?
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.
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?
@@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 ...
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…. 😢
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
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.
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 -
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.
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
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.
I really liked the thought process sharing. Thanks.
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 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.
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!
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
Thank you for this. Please give us more glimpses of real world data analytics/scientist work and how they solve problems through data
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.
Not as tricky as I expected , but I enjoyed how the guy thinks
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.
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
Really appreciate this video. Extremely helpful!
i am sooo lucky to pump into this video before the data science tech interview tomorrow!
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.
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
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.
andrew pretty much messed up the entire second query. There was no need to over-complicate it.
select sum(salary)
from employees
where id IN (
select distinct employeed_id
from projects
where end_dt is null
)
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.
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
The join would break the query causing salaries double triple, etc.
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)
These are super helpful. Thanks for this, Jay.
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
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
Question 2: sum(e.salary) and then group by employee_id? That's not producing what the question is asking for.
They both did wrong on the second question, they agreed on the definition that employees without projects on hand are not considered but Jay's answer included those employees.
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
)
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.
Very interesting thought process
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
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.
In the first question if employee table why did we drop cases with null end_date?
for problem 2 you never tacked case 3, when there is no projectid associated with an employeeid...
great video!! thank you.
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.
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.
which query of the two will perform better assuming you have a big number in terms of employees and projects?
Really productive session
learning SQL and this is very helpful
Excellent video!
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
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 😅
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
This is awesomeeee!
Great process
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
In the first question he is literally passing the time🤣
Got a interview tomorrow. I'm f*cked
lmao I hope it went well! How did it go?
They miscommunicated a couple times but very helpful
i dont think they r tricky at all. for any one who knows and uses sql in industry, they r basic.
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…. 😢
oo princes of data science :)
Wahahaha our royalty status is highly in question
@@AndrewMoMoney 😂
I can vouch for that
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
Cool
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