hi friends welcome back to another youtube video in today's video I'm actually starting the series
where i am going to be solving some lead code um sql problems as well as python um and i thought
it would be a good idea for me to kind of do it on the camera and record my screen so you all can
watch me while i'm doing it and hopefully you get to learn something in the process if you learned
something from this video make sure to give it a thumbs up and subscribe to my channel if you want
to hear more about data science tech lifestyle career journey and everything else in between
now i'm gonna actually start with lead code i don't have a premium version on the lead code i
have the free version on the free version there are few sql questions that are available with
different difficulty levels so what i'm trying to solve here today is medium the first one video
that i posted i solved an easy problem and some of you wanted me to do medium and hard problems
so let's do that i think the easy one was way too easy um but i'm sure like some of you people who
watch it they obviously learn something from it especially if you're new to sql so in today's
video we're going to solve three medium lead code problems if i run out of problem only code i will
jump to hacker rank and try to find intermediate sql questions all right with that i'm gonna hit
record on my screen and we can get started i just went into the code and um on lead code so i
clicked on problem and then i went on to databases because databases has sql related questions so i
clicked here and now i'm going to pick one problem i think majority of these are sql so i'm going to
change the difficulty level to medium and then from here i'm going to pick one of the problems
and do it together all right so this one looks fun uh where we have to find it's problem number
176 under databases second highest salary so let's click into it and solve it so let's
read the problem first uh the problem is basically there's an employee table so id is the primary key
in primary key column for this table each row of this table contains information about the
salary of them of an employee okay make sense um the problem that they're giving us is write
a sequel right and sql query is that good is that proper english write a sql query is it write a sql
query oh my god tmi all right never mind write a sql query to report the second highest salary
from the employee table if there is no second highest salary the query should report null
okay let me just think about it so they are let's say the company has employees
and the table has employees and they want to find the second highest paid employee
okay the query results is in the following example they gave us example which is
um this is what it looks like so there's basically id i'm guessing that's employee id
um and then the output is basically a single digit output what is what is the number of second higher
salary so amount of second higher salary okay then example two is basically employee table and then
output is zero so basically when the employee is when the table when the results has only one row
which means that there is no second then they want the null okay let's try to solve this let me just
say it out loud what i think that that this wants this basically sounds like since we have only one
table there's no join but from within this table i just need to find the second highest paid person
and um just by the sound of it it sounds like it's going to be an analytics function um the
part that i'm not sure about is is either it's a row number or a rank or a dense rank function so
what i'm going to do is like i'm going to try and see which one it is because like this whole
process of practicing is learning so let's do that all right so what i need to do first i'm going to
write my from statement which is from employee table e and then i'm going to do my select so
select is basically the id and the salary and um then i need that the analytics analytics function
that i was talking about um so that's what i need my first instinct is to go with the row number
let's see um let's see if that works like row number is it row number or row number
row number it's raw num okay over partition by so if for those of you who don't know what analytics
function are they're basically a powerful way to analyze a large sum of data smartly so um
it's not very difficult to learn but think of it like you are doing your group by
statement but doing all of that in one when in one line of code so think of it like a group by but
in a more smart fashion so if you have if you're not familiar with analytics function
like i would highly encourage you to get into that because that's pretty much what
it is like intermediate and advanced sql um is like working with analytics functions so
get used to that okay so for partition by this is where i need to think so they want the employee
so i we only have two two fields in the table so what i'm going to do partition by id because
that's what i want to segment by that's what i want to segment and i want to segment it by
salary and i want it in that descending order so d-e-s-v so
descending which basically means that i'll have the highest salary and the id next to it
all right so i'm gonna call it as rn and i'm gonna run it this is probably not gonna be
the right answer but i'm gonna run this anyway and see what i get
there is an error from the right syntax
i think this is this is supposed to be this run code
okay so the the function the row num it was row number because i'm using my
sql it seems like the answer is wrong but there's no syntax that are in the code so
let's look at the error again and see what's what is actually wrong so my input was this
the output that i'm getting is i'm getting id and row number so one two okay it seems like it's um
so okay i think what is happening is since i am partitioning by id
it's partitioning the id first and then the salary so it's ranking the id it's putting the row number
on the id so what i'm gonna do is i'm gonna remo remove this and i'm gonna run this again
let's see this works order by salary descending
and what i get
okay now at least when you look at the output now at least i'm getting the the order correct um so
the highest salary is getting one two and three um but the values that they're expecting is 200
so what i need to do is basically have another select statement outside of it so i'm going to
say select salary as the second highest salary which is i'm going to copy paste it from here
from this inside query from this and i'm going to call it b and i'm going to limit it to where
row number equals to 2. so let's see what i get here
so if i run the code i should get 200 which is the second highest salary okay well looks like
that is the solution um basically what i did is i use the row number function and assigned
row uh basically an id row number to each row that i'm getting for id and salary and
uh basically i the order i applied was descending so it starts from the highest and then goes
and low and then i created an outer query around it where i basically pulled the salary information
where with the filter row number equals to two so it gives me the second highest salary
all right so that was good so for the second problem let's go back to lead code um
right here uh okay so let's do that same thing again we're gonna switch to medium and databases
okay so it's already there so let's try to do one more uh let's do this one rank scores because this
could be a good ranking exercise today okay so for the problem number two what we have is a scores
table um id is the primary key for this table each row of this table contains the score of a game
score is a floating point value with two decimal places understood write a sql query to rank the
scores the ranking should be calculated according to the following rules okay there are rules rule
number one the score should be ranked from highest to the lowest got it if there is a tie between two
scores both should have the same ranking so that the third is after tied the next ranking
number should be the next consecutive integer value what after a tie the next ranking number
should be the next consecutive integer value in other words there should be no holes between ranks
okay that part i'm a little bit confused about okay the return the results table
ordered by score in descending order ordered by the score in descending order which is the query
result format is the following example so they want id one two three four five six score is
this and then the output is basically they're giving rank to each score
and okay so what i'm gonna do is i'm gonna stay from i'm gonna write my from again so
this is course table i'm going to call it s and what i want to select this this somewhat seems
similar to the last problem that we solved but i feel like there is a catch that i am probably
being on overconfident i don't know if i'm what i'm missing okay so select id and scores so this
will give me basically everything in the table but what i need is an analytics function to
basically find the score ranking so what i'm going to use actually i'm going to start with the
ranking function the rank function um and if that doesn't work then i'm going to try the dense rank
so rank over partition so i don't know if we need a partition but let's see the
output again so it wants score and then rank okay so what i want to do is order by score
score in desc and i'm going to call it as rank so and then i need to put an outer query around it
or do i actually i don't so i can remove this id so i need score plus rank that's all i need
right uh so here it can't be this easy but let's run the code ready three two one go
oh epic failure okay there's the syntax editor okay you have an error in your
sql syntax check the manual test blah blah blah rank is rank not a function anymore
so the mysql server version for the right syntax
okay let me copy paste this into oracle equal let's see if this works
okay so apparently there's a different syntax for my sequel which apparently i don't remember but
i basically copy pasted that code into oracle sql and it seems to be working but the answer
is not correct so at least i don't have to worry about this index syntax error right now because
it's all about the logic that i'm trying to improve on so it's saying that so this is
your my input and this is my output so my output is value id the score is four
and rank is one the score is four the rank is one the score is three point oh okay so i think what's
happening is that it wants the consecutive rank but here it seems like it's skipping skipping the
rank so it goes from one to three and and there's like no two here so let me try dance rank because
i know because i as i was saying like one of them does do the does do the duplicates in the run
oh ta-da okay so uh yeah so basically dense rank was the right function to use here
um what it's doing is basically looking at the score and going back to the score and basically
assigning consecutive numbers to each so here you can see like 4.0 and 4.0 is assigned one
three point eight point sign two three point six five is the sign three three
point six point is assigned three and then three point five is sign four
so awesome i don't know maybe i should move on too hard medium is not that doesn't seem
to be that difficult let's do the third one so let's see which one i want to pick
looks like this one i have solved already and the rest of them okay so i don't have many options
other than like these few so let's do this one
okay so they have already given me this framework and i need to write it inside it okay
cool so let's actually look at the problem first so again we have an employee table the id is
the primary key column of this table each row of this table contains information about the salary
of an employee so write a sql query to report the enth highest salary of the employee table
if there is no entire salary the query should report null doesn't it sound like similar to
what we did the first problem the query results format is the following example so this is the
input you get salary and what is this n equals to 2 i don't know what that is get the salary
oh so basically they're saying like if i say n equals to 2 then give me the salary what is the
second highest celery the second is this honestly like i think we did this one but i'm gonna do
it for the sake of it because this might look intimidating for to some of you so start with
my base table so i'm going to say from employee e and then i'm going to do my
select so for my select they are only expecting salary so i'm going to pull both id and salary
id and salary and then i'm going to say
i'm going to say row number row number why do i why do i keep typing row num okay a row number
over partition by i don't think i need a partition remember from last name what we did so i only need
an order by because i don't need to segment it by anything so i'm going to do a salary then i'm
going to do descending and i'm going to say as rn or n in this case so and then i need to put
yeah i think that's pretty much it right because there is a function around it
um that already is here so okay return result so the function is
create function get highest salary n in number return number is result
number okay i'm gonna run this code and see what the error are and then i'm gonna try to fix it
runtime error wow interesting this is the first one i've gotten there's no other thing what
i feel like i wish i had music right now why is there a runtime matter does anybody know
come on
what am i doing wrong i'm gonna copy paste this into my sequel okay
so create function get higher salary n is an integer
we which the function is going to pass and then return is this is the return right my
sql state statement so oh okay maybe i need to do a wrap around it so select
select salary
from this table
where
i'm going to call this table
just all this table bb oh my god i'm horrible with these names so bb so where n which is
what i'm getting equals to bb or you can just call it rn for the sake of it k week equals to
r n so now i'm only gonna get this based on the n okay
i really hope that gives me oh what wow that was so smart so this good job so basically what it is
is they have a function to give you highest salary based on the the rank that you input
it looks like i basically had to output only one number and then my where clause basically is this
is this information is getting passed in the function so i say where n equals to this number
and then it gives me the one output which is 200. so we did it three problems three medium equal
lead code problem so as you saw like the three problems that we saw today most of them were
around the theme of uh ranking and i use primarily analytics function i think in two of the problems
i used the row number and in one problem i had to use dense rank after trying the rank if you
don't know analytics function i would highly recommend you to get familiar with them because
they can be super super useful for writing these complex statements in very short number of code
short amount of code short amount of code small number of code small number of code less code
less lines of code that's what i mean to say i hope like watching me
practice watching me do these problems i hope you learned something along the way
let me know in comments actually what you learned today so with that thank
you so much for watching i hope you're having a beautiful day and i'll talk to you later bye