LeetCode 180 Consecutive Numbers SQL

what's up youtube today we're going to take look at elite curve problem 180 consecutive numbers mark this medium let's get into it so our task is to write sql query to find all numbers that appear at least three times consecutively and we have table called logs that has an id and number field is just called num and given this input one would be the only number that appears consecutively for at least three times because it appears for id one two and three so we're just going to assume that these ids don't have any gaps it's some sort of time step maybe maybe it's day and that just keeps going up and that should be our input so if you read consecutive in question you should probably think about self join or lag and lead functions which allow you to look at the row before your current row after your current row and self joints allow you to do the same by using different joint conditions we're going to use self join here because think it's great way to showcase self joins but in this case we're going to want to find ids that have at least three entries consecutively not just two so we're gonna self-join twice and triple join so say so let's get into it so we're gonna start out by selecting number so we're gonna use num from that table logs and we're gonna use it multiple times so i'm gonna call this one and select dot num so the first term and then start out by joining already so if we're self joining we unit we're going to use the same table again so it's going to be logs again we only have one table call that one and we're going to join on the following as said we want to look at consecutive numbers so we're going to use id to see which of these entries are consecutive so if id of is one higher than id of that means they're consecutive right because it's just one higher than the one before just like in the example so i'm gonna say dot id should be dot id plus one that means dot id is one higher than dot id and we will get first two rows for example easy as that we also want to join on the number so we want to make sure that we also have the same number because our task is to find numbers that appear three times consecutively at least so it needs to be the same number so dot num should be b.9 so that would be the solution for numbers appearing at least two times consecutively let's make it three times by just joining logs again call that one and that should be two higher than right and then also we should have the same number as well so in that case we established that we have an ida let's take one for example the following one should be two one higher than one and one after that should be three two higher than one and we want to output dot num it doesn't really matter here because it should be the same number which we're checking here so all of these should be one in our example and if we run that query that should give us an accepted output we just need to rename our output field as consecutive nums and that should work one thing we need to keep in mind is what happens if one appears at least three times twice or more than twice so what if there is id 8 9 10 and it's all one should we display it once or twice and think the way understand the question we should only show the one so we're going to use distinct here in order to just hold once if we submit that query we get an exempted answer and that's pretty much it for this problem already hope you were able to follow it's just basic self join done twice need to wrap your head around these join conditions but that is something you see quite often it's often used when comparing something to the following day for example the weather questions need code to compare tomorrow's temperature to today's temperature you're gonna use similar kind of self join based on the dates being one higher than the day before and in this case we're just gonna use id so this question is quite nice gave an upload hope you liked it too i'm trying to go through all eco database problems on this channel starting with the ones that are available without equal premium so everyone can follow along so there should be nothing stopping you from studying with me and if you want to keep learning just make sure to subscribe or stick around for the videos see you all in the next video bye