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

[@brianligat2038]

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

[@arihantdhadiwal9115]

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

[@techTFQ]

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

[@joe2271]

Seriously. You are awesome and explain it so well

[@techTFQ]

Thank you 🙏🏼

[@minalgupta7456]

I agree

[@racheldong60]

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

[@arindamnath1233]

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

[@techTFQ]

Glad you think so 🙏🏼

[@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

[@mananshah7095]

You are one of the best SQL instructor on KZbin. 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]

Glad to hear that Manah :)

[@reigngamer94]

Complex query explained in the best way

[@techTFQ]

Thank you :)

[@bhuwanthapliyal7977]

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]

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

[@InvisibleDivide]

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

[@karthikd2104]

Clear explanation ! Easy way to understand!

[@techTFQ]

Thank you Karthik 🙏🏼 Glad you liked it ..

[@ashabhumza3394]

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

[@techTFQ]

Thank you Ashab ☺️

[@abhilashs3160]

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

[@techTFQ]

Thank you Abhilash .. glad you liked it

[@vikranttyagi5232]

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

[@techTFQ]

Thank you Vikrant :)

[@pavangsk8404]

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

[@vikaskumar-qr5tj]

Beautiful explanation just crystal clear.....

[@techTFQ]

Thank you vikas

[@aashishmalhotra]

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]

Thank you :)

[@hungphutr]

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.

[@IMHitesh]

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

[@techTFQ]

Your welcome Hitesh 🙏🏼 Glad this helped you..

[@microvlog06]

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

[@SagarKumar-db2xy]

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

[@techTFQ]

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

[@TheIndianSocial]

best explanation for the streak concept.

[@sajidsshaikh707]

Yes, indeed your tutorials are really helpful man !!

[@techTFQ]

Glad to hear that sajid!

[@KrishnaYadav-ge5re]

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

[@tsukuruuu]

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

[@techTFQ]

Thank you

[@girijashamak9142]

One more gem of a content

[@techTFQ]

Thank you Girija for such kind words 🙏🏼🙏🏼

[@askarhameedka2953]

I cleared one assessment with this code. thanks

[@techTFQ]

That’s great to hear 😃 Congratulations 👏

[@salmanbaig15]

Terrific explanation.. Thank you!

[@siribn2657]

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

[@techTFQ]

Thank you Siri 🙏🏼

[@benoitpatigny]

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

[@techTFQ]

😃 Thank you Benoit 🙏🏼 Glad this helped ..

[@nileshkadivar6281]

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

[@techTFQ]

Thank you Nilesh

[@GreatNishan]

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]

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

[@tinurathi5210]

Thank you.. this is very helpful!

[@sivagssri]

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]

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

[@sonalivv]

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

[@techTFQ]

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

[@swatisinha3230]

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

[@techTFQ]

Thank you Swati 🙏🏼 Glad this helped ..

[@reddaiahreddymallu]

Thank you, Thoufiq.

[@fathimafarahna2633]

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

[@techTFQ]

Thank you ❤️

[@bibekrawat2284]

Such a nice concept.

[@techTFQ]

Thank you 🙏🏼

[@flwi]

That is a very clever approach imho! Well done!

[@purnamohan2991]

very helpful sir, thanks a lot.

[@KSudS_]

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

[@techTFQ]

Thank you 🙏🏼 Sure will plan it out..

[@piyushpathak7311]

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

[@techTFQ]

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

[@shoboyjr]

This is really helpful! Thank you!

[@karthikeyan_thangavel]

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

[@techTFQ]

Nice to know that .. glad this helped 😃

[@zaraahmed1597]

Loved the approach.Thanks for sharing :)

[@techTFQ]

Your welcome 🙏🏼 Glad this helped

[@TheRaju991]

This was super helpful 👍

[@MehmetEratli]

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

[@srt5806]

Crisply explained!

[@techTFQ]

Thank you 🙏🏼

[@hadireg]

Thanks mate! Very useful indeed

[@techTFQ]

Your welcome buddy

[@shivsharma9153]

REALLY ENJOYING CHANNEL!!

[@techTFQ]

Thank you Shiv 🙏🏼 Glad you like the channel 🙂

[@aneksingh4496]

Really good and nicely explained

[@techTFQ]

Thank you Anek 🙏🏼

[@flaviusioan69]

Great tutorial ... Thnx

[@techTFQ]

Thank you Frisan 🙏

[@prachijain5960]

Informative video. Please share some videos on outliers

[@techTFQ]

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

[@juhairahamed5342]

Good explanation

[@techTFQ]

Thank you :)

[@mohammedshahil4898]

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

[@techTFQ]

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

[@hanweithoo5008]

Tabibitosan method. Great stuff !

[@techTFQ]

Thank you HanWei 🙏🏼 Glad you liked it ..

[@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

[@sachinpatel-oq3ib]

learned something new 👍

[@techTFQ]

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

[@shafiquek9960]

Well explained.

[@techTFQ]

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

[@santhoshkumarj4648]

Genius 🔥🔥 pls post more videos

[@techTFQ]

Thank you Santhosh and will do 🙏🏼

[@sheebaparveen3630]

Great . Such good videos 🔥🔥💯💯💯

[@techTFQ]

Thank you cheeb ❤️

[@SudhirKumar-rl4wt]

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]

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

[@shahinurrahman7745]

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]

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 ..

[@nawalambavkar7543]

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

[@techTFQ]

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

[@divya001007]

Thank you so much for your best tutorials @thoufiq_sir , it was really an amazing explanation. Tqsm for ur efforts

[@anudeepreddy5559]

Superb Thoufiq ❤❤

[@sandeepkumar-ol5lg]

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

[@techTFQ]

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

[@bhavaniethirajan9627]

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]

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..

[@ameygoesgaming8793]

gold content

[@munna1431000]

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

[@techTFQ]

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

[@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

[@AraSh-yn2tt]

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]

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

[@belikk1986]

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

[@vishalsvits]

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

[@MrManda007]

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.

[@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.

[@quantum634]

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]

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 🙏🏼☺️

[@mohammadshahbaz3287]

@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?

[@pveeranjireddy8959]

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

[@techTFQ]

Thank you 🙏🏼 So glad you liked the content 🙂

[@harshitsinha4633]

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

[@databasewala6384]

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

[@nandhinisekar9689]

Awesome

[@techTFQ]

Thank you 🙏🏼 Glad you liked it 🙂

[@jasdeepsinghmonga4383]

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

[@priyanshu.tiwari]

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 :)

[@akhilkishore7361]

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

[@praneethmuragani]

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)

[@vishalrana9594]

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.

[@gparvez6695]

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.

[@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?

[@jackdesparrow4783]

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

[@kasturipal5522]

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

[@sql8758]

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.

[@shr_ee._________]

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 ..

[@MrRajat769]

How to find last 12 months sales from last visit date

[@unknown-ph4yb]

I have a query can u explain this please

[@techTFQ]

Sorry but I cannot assist anyone personally due to my time constraints

[@akhilgupta513]

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)

[@阿哲玩家]

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

[@hajarigupta121]

hello sir , i m recently started my MYSQL learning and following your learning but some where i get confuse regarding the query for cast the text in integer as it showing error and i m trying to do the same with CTE clause and join but i m not getting proper solution , so please guide me sir. question is : -- Finding n consecutive records with consecutive date value. i have try by this : create table dates( order_id varchar(20) primary key, order_date date); insert into dates values ('ORD1001', '2021-01-01'), ('ORD1002', '2021-02-01'), ('ORD1003', '2021-03-02'), ('ORD1004', '2021-02-03'), ('ORD1005', '2021-03-01'), ('ORD1006', '2021-06-01'), ('ORD1007', '2021-12-25'), ('ORD1008', '2021-12-26'); select * from dates; with Table1 as( select *,row_number()over(order by order_date)as id from dates ), demo as (select T1.order_id,T1.order_date from Table1 T1 join Table1 T2 on T1.id = T2.id+1 and T1.order_date =T2. order_date +interval 1 day ) select * from demo ; your solution : with t1 as (select *, row_number() over(order by order_date) as rn, order_date - cast(row_number() over(order by order_date)::numeric as int) as diff from dates), t2 as (select *, count(1) over (partition by diff) as cnt from t1) select order_id, order_date from t2 where cnt >= 3;

[@asifrehman8837]

What if after 6th primary key number, again there is another 3 consecutive records?

[@techTFQ]

this query would capture any no of consecutive records.. Let me know if you have a example where this would not work..

[@snehalsanap1750]

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]

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