Practice Complex SQL Queries | SQL Query to fetch N consecutive records

No video

Practice Complex SQL Queries | SQL Query to fetch N consecutive records

Рет қаралды 77,571

Күн бұрын

Practice Complex SQL Queries | SQL Query to fetch N consecutive records
In this video, I explain how to write a complex SQL query which is to fetch N consecutive records from a table based on some condition. We see how to write a generic SQL Query to fetch any number of consecutive records from a table.
We see 3 different variations of writing this query:
1) How to write this query when the table has a primary key column
2) How to write this query when the table DOES NOT have a primary key column
3) How to write this query based on Date field value.
All the queries and scripts mentioned in this video are posted on my blog. You can download them for free from below link:
techtfq.com/bl...
Timestamp:
00:00 Intro
01:08 SQL Query to fetch N consecutive records from a table which has a primary key
14:52 SQL Query to fetch N consecutive records from a table which does not have a primary key
18:46 SQL Query to fetch N consecutive records from a table based on date field
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
THANK YOU,
Thoufiq

Пікірлер: 189

@arihantdhadiwal9115 2 жыл бұрын

Amongst so many tutorials out there on youtube , hands down your's are the best. Will eagerly wait for your upcoming uploads . thanks

@techTFQ 2 жыл бұрын

Thank you Arihant 🙏🏼 Am so glad to hear you liked my contents 🙂

@joe2271 2 жыл бұрын

Seriously. You are awesome and explain it so well

@techTFQ 2 жыл бұрын

Thank you 🙏🏼

@minalgupta7456 2 жыл бұрын

I agree

@brianligat2038 Жыл бұрын

Have been doing SQL for years, but your clips are still very informative. Thanks.

@mananshah7095 2 жыл бұрын

You are one of the best SQL instructor on KZfaq. I have been using SQL for more than 2 years but still I have learnt so much from you. Please keep uploading the videos. Thank you!

@techTFQ 2 жыл бұрын

Glad to hear that Manah :)

@bhuwanthapliyal7977 2 жыл бұрын

No doubt your videos are more elaborate and better than institutes charging so much. One request, please create a series for SQL to cover end to end course

@techTFQ 2 жыл бұрын

Thank you Bhuwan.. glad you like it 🙏🏼 Yes I will make detailed SQL tutorial videos in the near future..

@aashishmalhotra 2 жыл бұрын

Perfect zoom for phone user , perfect mic , and perfect execution to approach a problem! Make more videos on companies SQL questions and your channel will definitely grow.

@techTFQ 2 жыл бұрын

Thank you :)

@guchhusworld587 Жыл бұрын

Thanks TFQ for your videos. Just wanted tweak the last part of the query to make it more generic. With t1 as ( select id,city,temperature,day, row_number() over (order by id) as diff, id - row_number() over (order by id) as conse from weather where temperature

@abhilashs3160 2 жыл бұрын

Great way of explaining complex concepts so that everyone can understand it clearly, you are a life saver.

@techTFQ 2 жыл бұрын

Thank you Abhilash .. glad you liked it

@vikranttyagi5232 2 жыл бұрын

All of your SQL videos are really helpful to understand the advanced concepts. Thanks for making them.

@techTFQ 2 жыл бұрын

Thank you Vikrant :)

@ashabhumza3394 2 жыл бұрын

Mashallah! You are really an SQL genius... 👍

@techTFQ 2 жыл бұрын

Thank you Ashab ☺️

@arindamnath1233 2 жыл бұрын

The best explanation for consecutive record fetching I ever get. Thank you

@techTFQ 2 жыл бұрын

Glad you think so 🙏🏼

@tsukuruuu 9 ай бұрын

midway on the video , i immediately clicked on subscribe. this is a gem!

@techTFQ 8 ай бұрын

Thank you

@IMHitesh 2 жыл бұрын

Thanks for solving this.... I was searching for the solution since so many days great effort... cheers

@techTFQ 2 жыл бұрын

Your welcome Hitesh 🙏🏼 Glad this helped you..

@hungphutr 2 жыл бұрын

thank you for the tutorial, super helpful!. for the last problem, I think we can increase the complexity by having multiple order records within a day. by that way, each orderID is no longer a unique indicator of a day for us to calculate the difference. I think there will be just one extra step that is to get to the current table structure like we have in this video by creating a new table grouped by order_date and generating a new id column using row_number.

@karthikd2104 2 жыл бұрын

Clear explanation ! Easy way to understand!

@techTFQ 2 жыл бұрын

Thank you Karthik 🙏🏼 Glad you liked it ..

@racheldong60 Жыл бұрын

this is a great presentation to explain how to pull N consecutive records! Thank you

@reigngamer94 2 жыл бұрын

Complex query explained in the best way

@techTFQ 2 жыл бұрын

Thank you :)

@SagarKumar-db2xy 2 жыл бұрын

Trust me, you are the best. Please post more videos on interview questions for beginners.

@techTFQ 2 жыл бұрын

Thank you Sagar 🙏🏼 Very happy to know you like my content 🙂

@sonalivv 2 жыл бұрын

This was brilliant! A perfect blend of math and SQL. Keep these videos coming!

@techTFQ 2 жыл бұрын

Thank you so much 🙏🏼 So glad you find this useful 🙂

@sivagssri 2 жыл бұрын

Wat to go... Such a crisp n clear explanation even for experience guy like me, got to see your channel and recommend to my group.

@techTFQ 2 жыл бұрын

Thank you Siva 🙏🏼 Happy to know you found some value from this video 🙂

@microvlog06 Жыл бұрын

your video helped me in solving leetcode hard problem. Thankyou!😀

@girijashamak9142 2 жыл бұрын

One more gem of a content

@techTFQ 2 жыл бұрын

Thank you Girija for such kind words 🙏🏼🙏🏼

@InvisibleDivide 2 жыл бұрын

Solution is so simple yet so big brain at the same time.

@nileshkadivar6281 2 жыл бұрын

Very well explained about problem statement. Watched many videos regarding SQL on your channel and it's easy to understand. 👏👌

@techTFQ 2 жыл бұрын

Thank you Nilesh

@swatisinha3230 2 жыл бұрын

Very helpful. I used to struggle with this, but this explanation is worth watching :)

@techTFQ 2 жыл бұрын

Thank you Swati 🙏🏼 Glad this helped ..

@KrishnaYadav-ge5re 2 жыл бұрын

I am very happy and excited to see more videos from you.. YOUR VIDEOS ARE AWESOME.. Great explainations

@vikaskumar-qr5tj 2 жыл бұрын

Beautiful explanation just crystal clear.....

@techTFQ 2 жыл бұрын

Thank you vikas

@GreatNishan 2 жыл бұрын

Nice video. I wish you post a continuation of this video by using “Preceding 1 and following 1” window clause and solving these questions.

@techTFQ 2 жыл бұрын

Thank you Nishan 🙏🏼 Glad you liked this video.. As for your suggestion, let me consider this and may be plan for a future video..

@KSudS_ 2 жыл бұрын

Great job. Pls we need to tackle more sql queries. If you can handle 10 questions per video, will be awesome. thanks.

@techTFQ 2 жыл бұрын

Thank you 🙏🏼 Sure will plan it out..

@pavangsk8404 Жыл бұрын

you have touth a very important problem in awesome way... brillliant sir

@siribn2657 2 жыл бұрын

Super helpful tutorial, brilliantly explained. waiting for more of these kinds..

@techTFQ 2 жыл бұрын

Thank you Siri 🙏🏼

@fathimafarahna2633 2 жыл бұрын

Amazing clarity and explanation👌 Keep up spreading knowledge. God bless 🙏

@techTFQ 2 жыл бұрын

Thank you ❤️

@quantum634 2 жыл бұрын

Hei it's me once again, just saw you answer to my comment in your previous Video and I was hei that's so cool from him..1 second later I see, you already uploaded the video haha :) Thank you for making an extra tutorial explaing this ! Very well done ! My question is answered completely. Keep up the good work. :)

@techTFQ 2 жыл бұрын

Hey … thank you so much 🙏🏼 I am so glad this video was able to answer your previous question 🙂 And thank you again for the feedback and for the appreciation 🙏🏼☺️

@bibekrawat2284 2 жыл бұрын

Such a nice concept.

@techTFQ 2 жыл бұрын

Thank you 🙏🏼

@benoitpatigny 2 жыл бұрын

Thank you for your awesome explanation, it's very useful, have a consecutive nice day

@techTFQ 2 жыл бұрын

😃 Thank you Benoit 🙏🏼 Glad this helped ..

@askarhameedka2953 2 жыл бұрын

I cleared one assessment with this code. thanks

@techTFQ 2 жыл бұрын

That’s great to hear 😃 Congratulations 👏

@sajidsshaikh707 2 жыл бұрын

Yes, indeed your tutorials are really helpful man !!

@techTFQ 2 жыл бұрын

Glad to hear that sajid!

@anudeepreddy5559 7 ай бұрын

Superb Thoufiq ❤❤

@shivsharma9153 2 жыл бұрын

REALLY ENJOYING CHANNEL!!

@techTFQ 2 жыл бұрын

Thank you Shiv 🙏🏼 Glad you like the channel 🙂

@piyushpathak7311 2 жыл бұрын

Sir we want more videos like this.. PLZZ upload..

@techTFQ 2 жыл бұрын

Thank you for liking my content Piyush 🙏🏼 Will try my best to upload more such contents 🙏🏼☺️

@ShivamTiwari-on2kl Жыл бұрын

I had a different approach using lead/lag window functions. So I will basically check if the lead/lag values are same as the current row values (both +ve or -ve) and if they are, i will assign them same ids using case statement

@user-nh8bg5po6v Жыл бұрын

Thank you so much for the amazing content! Are you planning to do more videos like this or where you solve stratascratch problems? Those would also be super helpful to see how you approach problems

@TheIndianSocial 5 ай бұрын

best explanation for the streak concept.

@purnamohan2991 8 ай бұрын

very helpful sir, thanks a lot.

@sudhindrab1606 Жыл бұрын

alternate solution for the last problem: select * from (select * , count(*) over(partition by substring(order_date,0,8)) as no_of_records from orders)s where no_of_records =1

@karthikeyan_thangavel 2 жыл бұрын

Very well helped at very critical situation... 🤗🥳🎊

@techTFQ 2 жыл бұрын

Nice to know that .. glad this helped 😃

@reddaiahreddymallu Жыл бұрын

Thank you, Thoufiq.

@mohammedshahil4898 2 жыл бұрын

Really very helpful🙌🏻👌🏻 Thank you for this yet another amazing video✌️

@techTFQ 2 жыл бұрын

Thank you hero 🙏🏼 I am glad to know you benefit from these videos 🙂😍

@flwi 2 жыл бұрын

That is a very clever approach imho! Well done!

@bhavaniethirajan9627 2 жыл бұрын

Hi.. first of all your way of explanation is awesome and easy understanding thanku for that.. one thing only couldn't understand like in ur example for pk ID u have given values as 1,2,3....n and row number will be 1,2,3....n so u can filter temperature less than zero n did subtraction between pk id and row number but what if pk id values will be starting like 2456,2457 n so on? How can we group this kinda values

@techTFQ 2 жыл бұрын

Thank you for the kind feedback Bhavani 🙏🏼 Even if ID is any other numeric value (as long as it is unique) , the logic would work fine.. In case the ID is non numeric then you can explicitly create an ID column (using row number) as I mentioned in one of the examples..

@zaraahmed1597 2 жыл бұрын

Loved the approach.Thanks for sharing :)

@techTFQ 2 жыл бұрын

Your welcome 🙏🏼 Glad this helped

@juhairahamed5342 2 жыл бұрын

Good explanation

@techTFQ 2 жыл бұрын

Thank you :)

@MohdSalmanBaig 2 жыл бұрын

Terrific explanation.. Thank you!

@shahinurrahman7745 2 жыл бұрын

Awesome explanation! Just one query- instead of partition we can use group by - having clause also, right? Any particular reason to use partitioning? Thanks again for all of your wonderful videos!

@techTFQ 2 жыл бұрын

Thank you 🙏🏼 Group by and partition by in window function are 2 completely different concepts.. this query is best solved using partition by .. Explaining the difference between these 2, cannot be done in comment but may need a separate video for it ..

@AraSh-yn2tt 2 жыл бұрын

That was nice bro, helped a lot, please keep going. Btw, could you post a video about how to calculate l, for example, growth rate in one specific column through time? In general, how to perform mathematical operations on grouped columns

@techTFQ 2 жыл бұрын

Thank you for the suggestion Ara.. Ill consider this for a future video..

@sheebaparveen3630 2 жыл бұрын

Great . Such good videos 🔥🔥💯💯💯

@techTFQ 2 жыл бұрын

Thank you cheeb ❤️

@Thesussyboi412 Жыл бұрын

Given the users purchase history write a query to print users who have done purchase on more than 1 day and products purchased on a given day are never repeated on any other day. Here is the ready script: create table purchase_history (userid int ,productid int ,purchasedate date ); SET DATEFORMAT dmy; insert into purchase_history values (1,1,'23-01-2012') ,(1,2,'23-01-2012') ,(1,3,'25-01-2012') ,(2,1,'23-01-2012') ,(2,2,'23-01-2012') ,(2,2,'25-01-2012') ,(2,4,'25-01-2012') ,(3,4,'23-01-2012') ,(3,1,'23-01-2012') ,(4,1,'23-01-2012') ,(4,2,'25-01-2012'); Can you provide the answer for above query?

@SudhirKumar-rl4wt 2 жыл бұрын

Thanks for the tutorial ..in the last solution ..I think it's the order_date column that establishes the fact that two rows are consecutive because order_id is varchar and comparing one order_id with another order_id doesn't make a lot of sense..so row_number() should be over order_date and not over order_id.because our final ouput should say which orders(order_id) are consecutive..we shouldn't make that assumption.

@SudhirKumar-rl4wt 2 жыл бұрын

Here Order_date should substitute ID(primary key) from first problem/solution

@harshitsinha4633 2 ай бұрын

21:28 in 3rd part, row number is itself an int how you converting if, (date- int )how it is working in date, we use datediff funct for difference

@hanweithoo5008 2 жыл бұрын

Tabibitosan method. Great stuff !

@techTFQ 2 жыл бұрын

Thank you HanWei 🙏🏼 Glad you liked it ..

@ameygoesgaming8793 4 ай бұрын

gold content

@mohammadshahbaz3287 6 ай бұрын

@techTFQ one question - for the case #2 scenario - without primary key, if u assign row_number with any order by clause, then its possible u would end up getting random ordering & might get wrong result, right?

@vishalsvits Жыл бұрын

Hi , at 17:16 . over clause must have order by.

@aneksingh4496 2 жыл бұрын

Really good and nicely explained

@techTFQ 2 жыл бұрын

Thank you Anek 🙏🏼

@shoboyjr Жыл бұрын

This is really helpful! Thank you!

@nawalambavkar7543 2 жыл бұрын

Hi Thank you amazing videos Learnt a lot from you Can you please suggest some websites for practising SQL queries for data science

@techTFQ 2 жыл бұрын

Thank you 🙏🏼 Not really sure if there is any website for SQL practice focused on data science but I would recommend using leetcode to practice SQL queries.. I’ve made a separate video about leetcode and how to find SQL questions on it. You can check that out on my channel

@sumitkumar-zv4xb Жыл бұрын

HEY TFQ, A little tweak in this problem how i can find the maximum consecutive days for which the temperature was less than 0.

@tinurathi5210 2 жыл бұрын

Thank you.. this is very helpful!

@srt5806 2 жыл бұрын

Crisply explained!

@techTFQ 2 жыл бұрын

Thank you 🙏🏼

@hadireg 2 жыл бұрын

Thanks mate! Very useful indeed

@techTFQ 2 жыл бұрын

Your welcome buddy

@santhoshkumarj4648 2 жыл бұрын

Genius 🔥🔥 pls post more videos

@techTFQ 2 жыл бұрын

Thank you Santhosh and will do 🙏🏼

@flaviusioan69 2 жыл бұрын

Great tutorial ... Thnx

@techTFQ 2 жыл бұрын

Thank you Frisan 🙏

@TheRaju991 2 жыл бұрын

This was super helpful 👍

@sandeepkumar-ol5lg 2 жыл бұрын

Amazing explanation, by the way you’re using a tool or any online editor to run queries .

@techTFQ 2 жыл бұрын

Thank you :) I use PostgreSQL database and PgAdmin tool to write queries

@belikk1986 Жыл бұрын

Great tutorial! What if i have multiple records per one day and i still need to count quantity of consequtive days?

@MrManda007 2 жыл бұрын

In the first case when you have an id and then do subtraction it can be done only in this dataset there is no math rule who will guarantee that subtraction id from the generated column will give you the numbers you get in your example. For example in my case id column is the same as generated column and the different field is always zero. 1- 1 =0 2-2=0 etc. You just find the way who will work on this dataset.

@shafiquek9960 2 жыл бұрын

Well explained.

@techTFQ 2 жыл бұрын

Thank you Shafique 🙏🏼 So glad you are liking my video 🙂

@vishalrana9594 2 жыл бұрын

A very informative video....just have a small doubt in the second part in which we don't have a primary key and we are assigning row numbers twice...In the first part, we had primary key that was different to row number but in the second case, both sets will be the same... So id-Row_num will give output as 0 for all...Please correct me if I am wrong.

@praneethmuragani 10 ай бұрын

Hi bro! here is my solution select id, city, temperature, day from ( select *, lag(temperature) over(order by day asc) as lg, lead(temperature) over(order by day asc) as ld from weather ) tbl where temperature < 0 and (lg < 0 or ld < 0) and (lg is not null or ld is not null)

@prachijain5960 2 жыл бұрын

Informative video. Please share some videos on outliers

@techTFQ 2 жыл бұрын

Thanks Prachi 🙏🏼 Let me consider your suggestion for a future video ..

@jackdesparrow4783 Жыл бұрын

I have a doubt in 1st query. When there is 2 consecutive I'ds having t

@munna1431000 2 жыл бұрын

@techTFQ Is there a way to get dump of data or database which you walkthrough in your videos ?

@techTFQ 2 жыл бұрын

Yes you can find it in my blog and I have provided the link to my blog in the video description

@sachinpatel-oq3ib 2 жыл бұрын

learned something new 👍

@techTFQ 2 жыл бұрын

Thank you Sachin 🙏🏼 So glad I was able to teach you something new 🙂

@priyanshu.tiwari 2 жыл бұрын

For Second Part, In MSSQL, this can be used: WITH T1 AS ( select *, ROW_NUMBER() OVER (ORDER BY order_id) AS RowNumber, DATEADD(day,-ROW_NUMBER() OVER (ORDER BY order_id),order_date) AS DIFF FROM orders ), T2 AS ( SELECT *,count(*) over(partition by DIFF) as no_of_records FROM T1 ) SELECT * FROM T2 WHERE no_of_records=3; Thank you :)

@pveeranjireddy8959 2 жыл бұрын

Great videos bro ... I subscribed channel...

@techTFQ 2 жыл бұрын

Thank you 🙏🏼 So glad you liked the content 🙂

@databasewala6384 2 жыл бұрын

Can you please make a video to delete duplicate records without using rowid.

@jasdeepsinghmonga4383 2 жыл бұрын

How to find the difference in MySQL for last problem? Not able to do it using Cast

@akhilkishore7361 Жыл бұрын

what if there are multiple cities and you need to get the temp group by each city

@shubhampandhare8866 2 жыл бұрын

Just out of curiosity What if the difference of 1 consecutive set and 1 individual date difference will be same In that case it will be partition by the same difference where this individual date difference may get ignored Please correct me if am not able to ask this properly Thanks for your valuable time

@techTFQ 2 жыл бұрын

Sorry bro.. I am not clear on what you asked.. Can you please elaborate?

@sql8758 2 жыл бұрын

Hi Thank you very much for the video. it's super helpful. I have one question, instead of create a new temporary table t2, can we just use t1 and GROUP BY? For example in the last example, after creating t1, I will just write: 'SELECT * FROM t1 GROUP BY no_of_records HAVING COUNT(*)=3' Does this work?

@guchhusworld587 Жыл бұрын

select * with a group by wont work

@arnabdas4056 Жыл бұрын

How can the logic work I didn't get it consider a date 2022-01-04 and I'd value 4 and temp say -1and I have to determine 4 consecutive days.it will break the entire logic.

@gparvez6695 2 жыл бұрын

Hello, I am working in mysql work bench. I cam up with an issue that when I use cast function to convert the rownumber to int i.e (date - cast(row_number() over()) as int ) as diff. Could you please help me solve with this issue.

@kasturipal5522 2 жыл бұрын

Hi , I have been seriously following your blog , but the solution is not working for me when solving leetcode 180 prob .With t1 as (select * , row_number() over(order by id) as rn, id - (row_number() over(order by id)) as difference from Logs ) , t2 as (select *, count(*) over(partition by difference) as no_of_records from t1) select num as ConsecutiveNums from t2 where no_of_records >= 3; Can you please check

@shr_ee._________ 11 ай бұрын

sir , how to find out customers who did orders in consecutive months ? when customer_id , order_id and order_date is given ?? please reply me .. i really need help ..

@sureshkumar-ou2fb Жыл бұрын

ACTUALLY WHAT IS IN SECOND PART (THAT WITHOUT PRIMARY KEY QUERY). THE FINAL OUTPUT WE GOT IN W ITSELF RIGHT? Y CREATING ANOTHER ROW_NUMBER WHICH GOING TO RETURN ROW_NUMBR -- ROW_NUMBER AGAIN 0 ONLY, AND WE TAKING PARTITION BY USING 0 DATA?? IN THE END WE DIDNT GOING TO GET ANY FILTERS RIGHT.. PLS EXPLAIN

@nandhinisekar9689 2 жыл бұрын

Awesome

@techTFQ 2 жыл бұрын

Thank you 🙏🏼 Glad you liked it 🙂

@akhilgupta513 2 жыл бұрын

For the solutions where you are relying on PK (ID/OrderId) to be in asc order to do the maths. Let's say PK (ID /Order ID) is not already sorted? (Except VW , where date is not given)

@user-jk6bf4qf7j 2 жыл бұрын

you can create another row_number in asc number as temp column and use it to do the remaining maths.

@snehalsanap1750 2 жыл бұрын

hi, while doing this 3rd part where only date is given both in mysql and ssms the cast operation over id and subtraction of it with date is giving error... operand type

@snehalsanap1750 2 жыл бұрын

select *, row_number() over(order by order_id) as rn, order_date - cast(row_number() over(order by order_id) as int) as difference from orders; error Operand type clash: date is incompatible with int - both in msql workbench and ssms.. how to solve? @ techTFQ