it's a good thing you're here because i wanted to make sure that you saw my little wooden pig uh her
name is uh chrissy p like as in chrissy p bacon anyway enjoy the video what's up you guys the most
requested the most popular video on my channel right now is amazon's most popular 2020 question
so you guys are probably wondering well what's amazon's most popular 2021 question and how can
andrew possibly predict that in the future well there's two ways one you can find a ton of the
questions that you are gonna be probably asked from lead code even in the free section of lead
code you're gonna find these questions number two i am able to cross reference
these questions on lead code with a database that i found on github about which companies
asks so this question is frequently asked by amazon adobe and google from my research i have
confidence that we are going to be answering the most frequently asked question in 2021
nostradamus style this is the big kahuna let's solve it together you and me i'm not only going
to answer questions like how long should you spend on each section of the question i'm also going to
answer questions like what clarifying questions should you ask the interviewer so that you keep
a conversation rolling and especially i'm gonna answer the question of how do you smash the
like button so that we defeat that youtube algorithm together and without further ado
this is professor meatball my name is andrew and we're going to get started right away
department top three salaries so the employee table holds
all employees every employee has an id and there is also a column for the department id
the department table holds all departments of the company we have to write a sql query to find
we have to write a sql query to find employees who earn the top three salaries in each of the
departments for the above tables your sql query should return the following rows orders of rows
does not matter okay so let us try and solve it there are usually three questions to ask in each
of these final round interviews you see this is a hard question on lead code that's probably the
level of difficulty of a final round interview something of an onsite or a virtual onsite if
you're in the pandemic still when you're watching this the main reason why there are three parts to
each question is that humans think in threes so the interviewer is going to be impressed that you
can break down the question into three individual parts if you really don't think that there's three
parts you can break it down to two but usually people don't just go and splat their brains all
over the page so to start this question i would talk to the interviewer hey a i would like to
take maybe three or four minutes to break down my thoughts and then i would start breaking it
down into these three sections first thing i would do is talk about the distinct salaries
of the table employee the reason why is because salaries could be the same right especially in
the this day and age when the market is competitive you can imagine that there's
multiple 80 000 salaries and we want to be able to compare distinctly each individual salary uh
by deduping all the 80 thousands into just one 80 000 value the second part is a sub query we want
to be able to write the sub query as a selection of all salaries uh that are greater than the outer
query the second step is tied to the first step we want to be able to count step one
for each salary in table employee where we aggregate by salary
in employee so we are going to group by salary as in and by this i mean the employee id
and what we're trying to do here intuitively is find out well for each person how many salaries
are greater than this person if there's more than a count of three salaries that is greater than
this salary then it means that it's not the top three because if it was a top three it would only
have zero one or two salaries that is greater than this salary part three is a tricky one because
we've already solved it for department agnostic queries but now we need to partition by department
so that's the tricky part and it might make you want to move into a window function to begin with
window functions are pretty tricky to play around with make sure that you know
it in the back of your mind just in case but if you can help it try to
avoid a window function because it can mess your flow up for now i'm just going to join the
department table to get that information once i have this part of the query finished i would turn
to the interviewer and say how does this outline look i would talk through each of the steps and
see how the interviewer reacts favorably or not favorably if you're wondering how i was able to
conceptualize each of these sections it just takes a lot of practice so what i recommend is
going down to lead code and practicing one free question every single day or a question in sql
and a question in python if you're trying to do the whole shebang of a data science preparation
camp and there's two types of interviewers in this situation one could try to stonewall you and just
try to show no information even the whites of his eyes are not going to be shown he's just going to
look down upon you and say well what do you think about that do you think that's right
okay and that could be the worst case scenario no interviewer is going to be
a mean person to you they're just trying to show how you react in a stressful situation where you
don't have all the answers in front of you and there's not a lot of feedback coming your way
the best case scenario is an interviewer that guides you on to the right path if you straight
off or gives you a definitive yes this is the best way you got it slugger this is a really good job
thank you for telling me your outline ahead of time always outline though no matter what type
of interviewer you have they are looking for the fact that you are able to outline so never go into
code directly but now that we've outlined let's break it down into code the first part of every
select statement is select so let's start with that select state salary from employee checking my
syntax that looks good so far now the first part is done we need to create that subquery for the
second part so count step one for each salary in table employee or aggregate by employee id
and employee so now we need two employee tables so we're going to call this one e one
and now this is a sub query
okay we are counting step one so we need to put this count outside of the distinct salary
so i'm going to label this sub for the subquery and now for the outer query i'm going to
select this query at the end of the day okay so for now we're gonna we work we
are gonna have department but department currently is uh off the table so department
let's x that out for now uh and then we have employee so it looks like it's employee name
so i'm going to have uh e2 i'm going to call this table e2 employee oh sorry it's just name
e2 name and then we want their salary so e2 dot salary okay so we've selected this
and we need to select this from uh employee table and that's e2
okay so i mentioned verbally that in this step we need to check whether or not the number of
distinct salaries the count of distinct salaries is greater than three so let's do this here so
where three is greater than this number and we're not quite done we need to make this sub query a
where e2 dot id is less than e1 dot id it's because we're trying to find
out how many of these e1 salaries is greater than the e2 salary that we're aggregating by
so currently this query should get us these two columns here employee and salary but we don't have
department yet because we need to join so remember the outline is really helpful because i would have
forgotten that in this step too so on the third step we need to join to the department table
so what we're going to do here is joins come after the from statement so we're going to join
what kind of join is it going to be so we know the department table holds all the departments
of the company so this table is a directory and this table the employee table is also a bit of
a directory except that this table is more like a ledger because it goes by employee by
employee whereas the department table is really just holding information so this table is more
restrictive but we only care about what goes into this table because that's the ultimate outcome so
this is just a normal join or an inner join to be specific okay so an inner join on department table
on and this is the tricky part well not really this is the part where we have to
join on a specific column for e2 it is the department oh derp apartment
id and for a department and i'm going to alias that as d put those as is in there as as
because it's id over here so the second part is tricky how do we get the department id information
into the inner query well it would be natural to think about a join as a crutch but it could
be even simpler than that we just need a where filter so we're going to filter add one more
where statement which is always and never two where statements in a row we are going to add uh
1 dot department id must equal e2 department id
department id there you go all right so before we move forward and submit this code there is
always proof checking to be made so if you can come along with me there are actually
three mistakes in this code i'll take a second to have you guys think about where we are going wrong
okay so hope you had enough time to figure it out the first thing to notice is that
we do not have department as in the department table it's actually name so we have to go
back and label this we can label this as a department but it has to be d dot de dot name
as department second thing is that we are comparing id instead of salary which is
what i mentioned before it's easy to get mixed up in the heat of an interview so that's why
you want to be able to go back and make sure that you are actually still following each of
the steps that you put up in the outline so instead of id we want to put salary
third type of mistake is the deadly typo so look at this depart mend id down here
whoopsie daisy these are three deadly mistakes that can catch your cat if you
wanted me to go over the entire video of mistakes you could possibly make
i'd be happy to do that just leave it in the comments down below as well as your like and
your subscription down at the door now let's run this code aha success nobody's perfect nobody gets
it right the very first time unless you do many repetitions and a lot of practice if you wanted
to follow along with me i really appreciate a like for this video if you laughed or cried or
done something productive with your day because of this video if you want a step-by-step full guide
on what to do with your first 66 days of data or a 21 day boss mode head over to patreon i'm
giving my patrons a google doc of every single day what to do to become a six figure data scientist
in six months today's trivia question is about sql it is what is the difference between a sub
query and a cte common table expression would both have worked here leave your comments down
below of what you think the right answer is and i will jumble them up and pick an answer to give
a free consultation to last consultation i gave out was to enrique loreano we talked about data
science and his trajectory as an aspiring data scientist he's a really swell guy and
i appreciate him being an active participant in the live chats and the comment section down below
if you want your brand to be featured in a future video then go ahead and screenshot this video tag
andrew mo money on your instagram story and you can win a chance to be featured in a future video
as always this is part of the 30 day challenge where i upload a video every day for the month
of december so i'd appreciate it if you help my retention and click-through rate by watching a
video all the way through to the end and turning on notifications so that you can keep up and
stay up with my upload schedule check down the description for four free stocks in the month of
december if you sign up with weevil and deposit a hundred dollars into your investment account it's
still your money you can always take it out if you don't feel comfortable and as always my name
is andrew you guys are appreciated for now but not for forever i'll see you guys next time peace
department top three salaries