Practice Complex SQL Queries | SQL Query to fetch N consecutive records
HTML-код
- Опубликовано: 25 июл 2024
- 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/blog/learn-how-to...
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
Have been doing SQL for years, but your clips are still very informative. Thanks.
Amongst so many tutorials out there on youtube , hands down your's are the best. Will eagerly wait for your upcoming uploads . thanks
Thank you Arihant 🙏🏼
Am so glad to hear you liked my contents 🙂
Seriously. You are awesome and explain it so well
Thank you 🙏🏼
I agree
this is a great presentation to explain how to pull N consecutive records! Thank you
This was brilliant! A perfect blend of math and SQL. Keep these videos coming!
Thank you so much 🙏🏼
So glad you find this useful 🙂
I am very happy and excited to see more videos from you.. YOUR VIDEOS ARE AWESOME.. Great explainations
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.
Great way of explaining complex concepts so that everyone can understand it clearly, you are a life saver.
Thank you Abhilash .. glad you liked it
That is a very clever approach imho! Well done!
Clear explanation ! Easy way to understand!
Thank you Karthik 🙏🏼
Glad you liked it ..
Terrific explanation.. Thank you!
All of your SQL videos are really helpful to understand the advanced concepts. Thanks for making them.
Thank you Vikrant :)
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
you have touth a very important problem in awesome way... brillliant sir
The best explanation for consecutive record fetching I ever get. Thank you
Glad you think so 🙏🏼
This is really helpful! Thank you!
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
Thank you Bhuwan.. glad you like it 🙏🏼
Yes I will make detailed SQL tutorial videos in the near future..
Yes, indeed your tutorials are really helpful man !!
Glad to hear that sajid!
Very helpful. I used to struggle with this, but this explanation is worth watching :)
Thank you Swati 🙏🏼
Glad this helped ..
Thank you.. this is very helpful!
best explanation for the streak concept.
Thank you, Thoufiq.
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.
Thank you Siva 🙏🏼
Happy to know you found some value from this video 🙂
Super helpful tutorial, brilliantly explained. waiting for more of these kinds..
Thank you Siri 🙏🏼
very helpful sir, thanks a lot.
Amazing clarity and explanation👌 Keep up spreading knowledge. God bless 🙏
Thank you ❤️
Beautiful explanation just crystal clear.....
Thank you vikas
Thanks for solving this.... I was searching for the solution since so many days
great effort... cheers
Your welcome Hitesh 🙏🏼
Glad this helped you..
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
Solution is so simple yet so big brain at the same time.
You are one of the best SQL instructor on RUclips. 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!
Glad to hear that Manah :)
Really very helpful🙌🏻👌🏻 Thank you for this yet another amazing video✌️
Thank you hero 🙏🏼
I am glad to know you benefit from these videos 🙂😍
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.
Thank you :)
Very well explained about problem statement. Watched many videos regarding SQL on your channel and it's easy to understand. 👏👌
Thank you Nilesh
Mashallah! You are really an SQL genius... 👍
Thank you Ashab ☺️
your video helped me in solving leetcode hard problem. Thankyou!😀
Thanks mate! Very useful indeed
Your welcome buddy
midway on the video , i immediately clicked on subscribe. this is a gem!
Thank you
This was super helpful 👍
Thank you for your awesome explanation, it's very useful, have a consecutive nice day
😃
Thank you Benoit 🙏🏼
Glad this helped ..
Loved the approach.Thanks for sharing :)
Your welcome 🙏🏼
Glad this helped
Complex query explained in the best way
Thank you :)
Trust me, you are the best. Please post more videos on interview questions for beginners.
Thank you Sagar 🙏🏼
Very happy to know you like my content 🙂
One more gem of a content
Thank you Girija for such kind words 🙏🏼🙏🏼
Such a nice concept.
Thank you 🙏🏼
Nice video. I wish you post a continuation of this video by using “Preceding 1 and following 1” window clause and solving these questions.
Thank you Nishan 🙏🏼
Glad you liked this video..
As for your suggestion, let me consider this and may be plan for a future video..
Very well helped at very critical situation... 🤗🥳🎊
Nice to know that .. glad this helped 😃
Great job. Pls we need to tackle more sql queries. If you can handle 10 questions per video, will be awesome. thanks.
Thank you 🙏🏼
Sure will plan it out..
Superb Thoufiq ❤❤
Great . Such good videos 🔥🔥💯💯💯
Thank you cheeb ❤️
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
Thank you for the suggestion Ara.. Ill consider this for a future video..
REALLY ENJOYING CHANNEL!!
Thank you Shiv 🙏🏼
Glad you like the channel 🙂
I cleared one assessment with this code. thanks
That’s great to hear 😃
Congratulations 👏
Great tutorial ... Thnx
Thank you Frisan 🙏
gold content
Tabibitosan method. Great stuff !
Thank you HanWei 🙏🏼
Glad you liked it ..
Good explanation
Thank you :)
Well explained.
Thank you Shafique 🙏🏼
So glad you are liking my video 🙂
learned something new 👍
Thank you Sachin 🙏🏼
So glad I was able to teach you something new 🙂
Genius 🔥🔥 pls post more videos
Thank you Santhosh and will do 🙏🏼
Really good and nicely explained
Thank you Anek 🙏🏼
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!
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 ..
Crisply explained!
Thank you 🙏🏼
Sir we want more videos like this.. PLZZ upload..
Thank you for liking my content Piyush 🙏🏼
Will try my best to upload more such contents 🙏🏼☺️
Informative video. Please share some videos on outliers
Thanks Prachi 🙏🏼
Let me consider your suggestion for a future video ..
Great videos bro ...
I subscribed channel...
Thank you 🙏🏼
So glad you liked the content 🙂
Awesome
Thank you 🙏🏼
Glad you liked it 🙂
Great tutorial! What if i have multiple records per one day and i still need to count quantity of consequtive days?
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
Hi Thank you amazing videos
Learnt a lot from you
Can you please suggest some websites for practising SQL queries for data science
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
Amazing explanation, by the way you’re using a tool or any online editor to run queries .
Thank you :)
I use PostgreSQL database and PgAdmin tool to write queries
Hi , at 17:16 . over clause must have order by.
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.
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
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..
@techTFQ Is there a way to get dump of data or database which you walkthrough in your videos ?
Yes you can find it in my blog and I have provided the link to my blog in the video description
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.
Here Order_date should substitute ID(primary key) from first problem/solution
HEY TFQ,
A little tweak in this problem
how i can find the maximum consecutive days for which the temperature was less than 0.
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.
what if there are multiple cities and you need to get the temp group by each city
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?
select * with a group by wont work
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. :)
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 🙏🏼☺️
@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?
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
Can you please make a video to delete duplicate records without using rowid.
How to find the difference in MySQL for last problem? Not able to do it using Cast
I have a doubt in 1st query. When there is 2 consecutive I'ds having t
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.
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
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.
I have a query can u explain this please
Sorry but I cannot assist anyone personally due to my time constraints
How to find last 12 months sales from last visit date
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)
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 :)
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 ..
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
What if after 6th primary key number, again there is another 3 consecutive records?
this query would capture any no of consecutive records..
Let me know if you have a example where this would not work..
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
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
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
Sorry bro.. I am not clear on what you asked..
Can you please elaborate?
Hello, Thank you for your help,
I hope you can help me out sir,
I have 2 tables linked with user_id.
Table 1: user_id, name, Email, Phone
Table 2: id, user_id(from table 1), relative_name, relative_email, relative_phone
I created a query to list all `name` from table 1 into a dropdown list.
I am trying to make another column where data gets automatically populated from the corresponding name selected from table 1
For example:
Selected name A from dropdown list and want to show corresponding relative_name into another column based on the name I selected from Table 1.
I hope you can help me out.
Thanks in advance
18:47
hello sir
can you help me for a query
sorry i do not provide personal support