Real SQL Interviews: Amazon's 2020 MOST ASKED question

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

Channel: AndrewMoMoney

Views: 30,157

Rating: 4.9479394 out of 5

Keywords: amazon coding interview question, google coding interview question, facebook coding interview question, leetcode questions, leetcode problems, leetcode solutions, leetcode python, amazon coding interview, amazon interview, leetcode java solutions, leetcode python solutions, 626. Exchange Seats, leetcode SQL, amazon exchange seats, leetcode exchange seats, kevin naughton google, kevin naughton amazon, nick white, amazon data science, amazon interview preparation, andrew mo

Id: NUB15MexYXA

Length: 8min 42sec (522 seconds)

Published: Sat Aug 08 2020