If it's odd and it's not the last id then
we have to increase so abbott became from id number one to id number two then
please leetcode gods grant me safe passage on this amazon
technical interview okay can you see me okay hi hi um
thank you for having me thank you so much for
making the time today uh we're gonna jump straight into it so
sure let's just let's just get started what i wanted to start going over
is the premise so this is about Mary she's a teacher in middle school and she
has a table seat storing students names and their
corresponding seat id okay the column id is a continuous
increment and Mary wants to change seats for the
adjacent students so can you write a sql query to output
the result for mary okay you just take as long as you need so
first thing i'm going to think of is what kind of subquery do i want
to arrive at first the subquery that i think i'm going to arrive at
is i need to take a count of all of the student ids because what i
want to do is offset odd number ids
and add one and then even number ids and then subtract one this makes
sense if there are only two students for
example the number one student would become number
two and then the number two student would become
number one now this is a little tricky if you are the last student if you're
the last student then you don't get to move for example
if there's one two three we would swap one and two
but then three would be the last student still so in that case
i think i have a good understanding of where to start my sub query
is going to be a count of all students so i have that number
ready and then i'm going to build a case when case when the id
is odd then we will add one to the id unless it equals the
count of all student ids in which case it
means it's the last one in that case we keep it where it is at
the last place if it's even then it doesn't matter if
it's the last place all we need to do is subtract one so
i think i'm gonna start preparing that does that sound okay
all right let me start preparing the code so
it seems like we want we have this table and
we want to output this table the first thing i'm noticing is that
everyone who is odd shifts up one id value so
abbott over here at one goes up one value to two
and then everyone who is at an even id goes down one value so
doris goes down to abbott's id number which is one except if the
last person is odd it looks like
james does not move because there's no one to switch with that makes
sense so the first thing i want to start building for this table
is a sub query that i'm going to call total counts
and the reason for that i'm going to need to know how many ids are in this
table if there is an odd number of ids then we need to make sure we don't
switch that id and the way that i want to build
out that logic is somewhere along the lines of
select count star from table and in this case the table is seat
and this is assuming that there's only one
distinct id per student so i'm going to go off of
that assumption and i'm going to call that tc and it's
going to have nothing else because all i'm looking for is one final
number and this for that above table would be five and i would
use this to measure up against whether or not the
current id that i'm iterating through is going to be the last id okay
so let's start building actual code select
and let's start select starting with a case statement so this
case when and the first one is going to be if it is an odd
id that is the last odd id so in that case we would use the mod
function here and we would take the id and test if it's
even bang equals means not equal to zero in that case there would be an odd
id we also need to make sure and that it's
not the last one so let's assume that we built out the
sub query i'm gonna have this column be called tc
so the id equals tc
then it's odd it's the last value which means we don't flip it
we keep it as id
the second case statement is if it's an odd
if it's odd and it's not the last id then
we have to increase so do abbot became from id number one to id
number two then they're going to be id plus one
that's whatever their id was plus one so one becomes two
finally there's an else condition that is if they had an even
id then we would want them to be id minus one so doris up here who had an
even id would become one that is exactly her
old id two minus one so there we go close this up
and give it an alias so i'm gonna put it in parentheses and
i'm gonna call it as id okay and
let's not forget that these students have names so let's
give it student as the second column that we want to select
and then from the good old table seat and i'm going to give it a comma because
here's the time for our sub query this sub query would be the select count
star from seat right and give it the alias that i uh
said i was going to give it uh as tc right and then we can call this table
anything we want let's call it total counts
like we said up here that's the table that we after mentioned
and now we have built it down here as a sub query okay finally
we need to make sure that they're ordered because right now each idea
swaps is going to go like it's still going to have the same
student order but the ids are all going to be mismatched
so we have to order by id all right i think that looks good
let's run it
okay looks like it's working good
and submitted memory usage lesson 100 of my sequel online submissions i think
we have an answer here well that's that's great that's all we
needed today thanks so much for your time so last
thing i wanted to to understand is if this skit made you
laugh at all i think you owe it to the creator of this
sketch to drop a like you don't even have to smash the like button you can
just tap it nice and gently and if this would be helpful to anybody
else go ahead and share with a friend share
with a family member share with an enemy until next time
peace
you