Solving Complex SQL Query from HackerRank

Solving Complex SQL Query from HackerRank | SQL Interview Query

Рет қаралды 75,237

Жыл бұрын

In this video, we will learn how to solve a complex SQL query. The SQL problem is taken from HackerRank. We shall use recursive sql to solve the given problem.
Link to HackerRank problem:
www.hackerrank.com/challenges...
Download the scripts, dataset and solution used in this video from my blog. Link below:
techtfq.com/blog/solving-comp...
Join my SQL Course:
www.lighthall.co/class/8b7467...
View the course content here:
techtfq.com/my-sql-course
If you want me to solve any other SQL problem, please email me techtfq@gmail.com
🔴 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

Пікірлер: 172

@vishwanath-ts Жыл бұрын

The way you explained everything in detail I subscribed immediately. This is how SQL need to be taught

@techTFQ Жыл бұрын

Thank you :)

@alishakumari3663 Жыл бұрын

I have seen many solution of this question. But finally my search comes to an end i understand each and every thing crystal clear.

@data-dynamo-guy Жыл бұрын

I am very grateful for what you did in this video. Wish your channel grow more !

@markobondaruk4213 Жыл бұрын

I love the way how you splitting tasks, that really make sense and helps to organise things in your head. Thank you for the video.

@techTFQ Жыл бұрын

very glad to hear that :)

@SwethaNandyala-sf9lt Жыл бұрын

I was struggling with the recursive queries concept u made it so easy to undestand...and with that i was able to solve the question very easily

@srinivasv3282 Жыл бұрын

Thank you so much.. I was looking for this from some time now. Your style of explanation is awesome. Keep up the good work.

@pablogalindo2089 Жыл бұрын

This was an incredible explanation! Thank you so much!

@hilarylomotey7051 Жыл бұрын

The best SQL teacher ever, awesome man , u rock too much

@techTFQ Жыл бұрын

Thank you so much :)

@deepikavickraman505 10 ай бұрын

Very nice explanation and learnt recursive sql query 👌

@mateuszsmendowski2677 Жыл бұрын

Great explanation and awesome practical example that distinctly illustrates the purpose of recursive sql queries!

@techTFQ Жыл бұрын

Glad you liked it

@bnatara Жыл бұрын

You are doing a great job!!!

@mohammedshahil4898 Жыл бұрын

Love your these kind of videos where you show us how to solve a complex sql query👌Thanks a lot for such amazing videos🙌🏻🙏

@techTFQ Жыл бұрын

Glad you like it bro 😍

@prernaaneja3941 28 күн бұрын

Thanks, this was very helpful.

@1112electronics Жыл бұрын

Thanks for the great video on SQL you always producing.... awesome..please keep it up.

@techTFQ Жыл бұрын

Your welcome. happy to know it helps

@simranagichani4943 11 ай бұрын

Great simplification of such a complex problem and also thank you for adding the error in hackerrank, even I faced that and was confused initially whether to consider this solution or not. But thanks for clarifying that this might be an issue with the SQL version used in hackerrank

@yogeshmishra261 Жыл бұрын

Hi Taufiq, Your way of explanation on how to solve complex sql problems is real good and easy to understand. Also, i liked that you started with basic syntax of recursive cte for evryone to understand. I will be trying to use your way of deduction in my projects going forward. Thanks and keep going.

@techTFQ Жыл бұрын

Thank you :) Very glad to read this

@supriya1316 Жыл бұрын

Wow... You made this video... I was facing problem with this question!

@techTFQ Жыл бұрын

Glad I could help!

@qurdedu4032 Жыл бұрын

Another awesome content from Tofig, Thank you so much. Clicked too much the liked button and it’s broken now. 😂😂😂😂

@techTFQ Жыл бұрын

haha Thank you bro :)

@malateshhkulkarni4800 Жыл бұрын

Thank you sir..i don't know if anyone can better explain this..👌

@techTFQ Жыл бұрын

Thank you bro :)

@laurak5196 Жыл бұрын

Thoufiq I just wanted to say hi, and congratulations……SO close to 100K, way to go, friend!!

@techTFQ Жыл бұрын

Thank you Laura ☺️ Very happy to see your comment.. I hope things are going good for you.

@ganeshgaitonde5971 Жыл бұрын

thank you so much sir 🥰😍 ............it Helped me a lot to understand concepts of SQL more deeply🌹🌷🌼

@Anonymous-le2zr Жыл бұрын

Hello Thoufiq, Can you please make a video on SQL Query optimization? What things to be considered before writing query or how we can rewrite existing query for better execution time ? Please just consider query structure only (Not the changes on DB side like defining correct DataTypes, Indexes etc) Thanks a lot in advance ☺️

@techTFQ Жыл бұрын

Good suggestion, ill consider it

@CesarGarciaJara Жыл бұрын

Thanks a lot for your video. 👍🏼

@techTFQ Жыл бұрын

Your welcome, hope you liked it

@dheerajlakkakula5983 Жыл бұрын

Hii sir, Actually i came across this query while practicing ,but i didn't get the correct answer ,Now i understood.Thank you so much sir..

@techTFQ Жыл бұрын

Glad to hear that Dheeraj

@macdemon3486 Жыл бұрын

Brilliant!!

@techTFQ Жыл бұрын

Thank you 🙏🏼

@firdosesyed8981 Жыл бұрын

Hello Sir I'm a new learner tumhare classes bohot ache hin sir. Mai new se start kari hun SQL and ur classes r helping me a lot to become Power BI developer need SQL mandatory hai na so I'm learning from scratch thank you so much😊 for such a beautiful classes

@techTFQ Жыл бұрын

Your welcome Firdose 🙏🏼 Sunke acha laga 👍

@nehalahmad5678 Жыл бұрын

Just loved it the way you explain....Plz make a video on REGEXP in Oracle DB ... Thanks Taufeeq

@techTFQ Жыл бұрын

glad you liked it and noted on the request

@kancharalaparameshwarreddy3837 Жыл бұрын

Thanking you sir for more information

@techTFQ Жыл бұрын

Your welcome bro

@homelessguy4143 Жыл бұрын

You are the best

@techTFQ Жыл бұрын

thank you buddy

@fathimafarahna2633 Жыл бұрын

Fabulous ❤❤❤

@techTFQ Жыл бұрын

Thank you ☺️

@enjoyfriends873 2 ай бұрын

Thank you 😅 second part was the one which i could not able to implement. Now got some idea will implement it. First part i did with dense rank and inner join got output of first part 😂

@vivekmahadevan9838 Жыл бұрын

Interesting problem and good example on how to use recursion. Can you please share the query to create the tables required for this problem?

@techTFQ Жыл бұрын

Thank you :) and will share after few days

@vivekmahadevan9838 Жыл бұрын

@@techTFQ, can you please share the query to create tables for this problem?

@techTFQ Жыл бұрын

Have created a blog where I have provided the scripts.. you can download it from there. Link below: techtfq.com/blog/solving-complex-sql-query-from-hackerrank

@matts.4937 Жыл бұрын

Hi Taufiq, can you make a course on solving SQL challenges or solving interview questions on lighthall, udemy, or anywhere else?

@tejaspatil3978 Жыл бұрын

hey, plz make the video on youtube scraping data and store it in sql using python and beautifulsoup

@user-bf1se2ke2j 5 ай бұрын

Hi Thoufiq Bhai, Please explain and make a other video to write instead of RECURSIVE function? I mean without using Recursive function.

@VipinSingh-gz6pz 3 ай бұрын

Thank you very much for explaining. There is one problem, when i run the solution on sample data in sql-workbench on my computer, it works fine but when i submit the same solution on hackerrank it show error

@gkr9375 Жыл бұрын

Hi Thoufiq, I would like to know when will your next batch will start to learn SQL from beginner to Advance level. Planning to Join the course.

@techTFQ Жыл бұрын

probably early next year

@gkr9375 Жыл бұрын

@@techTFQ Next Year /month ?😇

@vartalap08 Жыл бұрын

Can you explain the count(1) meaning ??

@abhimanyutiwari100 Жыл бұрын

Hi Toufiq, can you make a video on the current industry salary for data analyst acc to exp in India?

@techTFQ Жыл бұрын

may be in the future bro.. salary varies from company to company so difficult to predict

@abhimanyutiwari100 Жыл бұрын

@@techTFQ sure toufiq.

@rohitmeshram4493 Жыл бұрын

Hello sir, Is it mandatory to practice tricky query for data analysis role?

@Explorecommonman-je6vu 5 ай бұрын

TFQ Bhai, Would you please create a video on Hacker scenario with out using Recursive function. Since I couldn't do it. Please help me on this video hackerrank scenario.

@muneess2979 Жыл бұрын

How to use if condition in with clause along with recursive.. Can u give me an example

@user-bf1se2ke2j 5 ай бұрын

Hi Thoufiq Bhai, Please explain and make a other video to write instead of RECURSIVE function? I mean without using Recursive function. In my server RECURSIVE function is NOT working. Please response and provide the alternative suggestions. Please make a video on the same scenario.

@DarkWebX-f Жыл бұрын

when was the next batch started ?

@maheshsharma-zq2uc Жыл бұрын

Hi any idea how to get minimum and maximum value together for particular month

@lakshmanp6256 Жыл бұрын

Sir can you please make a video on masking middle 4 values of phone number with letter x so it's like 978xxxx345 be in output

@techTFQ Жыл бұрын

Let me consider it

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

Hey Taufiq, you were not supposed to use CTE for this particular problem. You have to use only subqueries and joins

@kardiiobi321 Жыл бұрын

hi my brothr i have been foolowing you a lot I am actually new into the data analysis world and I want t ask you a question about sgl is it a very hard language to learn please can someone with my background of no educational like data or coding learn fast and get a dream job

@sharukhbilal5710 Жыл бұрын

Hi toufeeq, Can you kindly tell me from where I can learn regular expressions for oracle sql platform

@techTFQ Жыл бұрын

I will probably make a video about it soon

@anburenganathan8369 Жыл бұрын

more clarity on my question is for 3rd iteration. what will be the cte.submission_date . wheather is 2nd iteration submission date or base query remains same.

@techTFQ Жыл бұрын

base query just executes once (the first iteration). After that from 2nd iteration, only the recursive part of the query executes (the query below the UNION operator). During 2nd iteration, output from 1st iteration will be passed. During 3rd iteration, output from 2nd iteration will be passed. During 4th iteration, output from 3rd iteration will be passed and so on. I would recommend watching my recursive sql queries tutorial video for complete understanding.

@anburenganathan8369 Жыл бұрын

@@techTFQ Thanks thoufig for the clarification.. Now am cleared

@muneess2979 Жыл бұрын

How to make bulk insert brother?

@adrianusqueiroz Жыл бұрын

Hi Taufiq, Is not possible to use COUNT(DISTINCT hacker_id) and use unbounded and preceding rows? Does it solve column2 problem?

Ай бұрын

Range between starting row and unbounded following you mean ? That was also my intuition

@RajeevKumar-st5hr Жыл бұрын

Hello sir, currently I am using dynamic query. Query has set of multiple queries and all queries combined by union all. At end execute appended queries. I am getting error. What is the limitations of union all in dynamic queries?

@shreeratan567 Жыл бұрын

Bro there is a youtube Telugu channel which focuses on it manager vs employee differences which uses your name as scolding

@techTFQ Жыл бұрын

Haha is it which channel n video

@shreeratan567 Жыл бұрын

@@techTFQ Sai Kiran yediya

@anburenganathan8369 Жыл бұрын

Hi Thoufig.. Thanks for the video.. i have one question ..how it would check for 3rd iteration, whether base query always remain the same date or it will change for every iteration. if the base query remains same how it would return 3rd day(2016-03-03) for the condition (submission_date > cte.submission_date)

@ckarkhan 4 ай бұрын

I think you need to watch Thoufiq's video on recursive CTEs. In summary, first iteration is the base query execution. Just once. Second iteration is the recursive part of the CTE with input from the first iteration query. Third and all subsequent iterations run the recursive query with input from the 'prior' iteration of the recursive query. This is how the 'loop' iterates until it hits the termination condition in the WHERE clause or the JOIN clause if present. Feel free to correct me if I have missed anything in the flow.

@quickfireuk Жыл бұрын

Hi sir, what ide are you using postgreSql editor, thank you for the video

@techTFQ Жыл бұрын

I use PgAdmin

@quickfireuk Жыл бұрын

@@techTFQ Thanks for the update

@santoshmhamunkar2256 Жыл бұрын

"WITH recursive" clause works in Oracle?

@tuma8620 Жыл бұрын

Hi sir, to attend for interview your sql videos enough or not

@yassirnakkach5171 Жыл бұрын

Hi guys, can anybody share the query that creates the tables required for this exercice so we can test by ourselves? Thanks in advance!

@Explorecommonman-je6vu 5 ай бұрын

TFQ Bhai, Would you please create a video on Hacker scenario with out using Recursive function. Since I couldn't do it. Please help me on this video hacker rank scenario.

@Explorecommonman-je6vu 11 күн бұрын

I posted 4 months back and still haven't received any response from you. Please provide an alternative solution for the same scenario.

@abbasmusawis Жыл бұрын

Can you please help me to write some queries?

@SantanuRoy-oq2nv Жыл бұрын

Hello Taufiq Sir, without recursive keyword can you try?

@cryptoskool7322 Жыл бұрын

Hi can you make a query to get data of table from 16th of last month to end of month if query is hitted on 1 of current month and if we hit the same query on 16 th of current month we got the data of curr month from 1 to 15 . The constrains is only have to make one query for this + query must be independent of year

@avinash7003 Жыл бұрын

how to write in oracle?

@tessythomas5704 Жыл бұрын

Hi Taufiq Can you please let me know when is your next sql batch will start?

@techTFQ Жыл бұрын

Probably early next year.. not finalized yet

@tessythomas5704 Жыл бұрын

Just want to clarify, I saw one upcoming class on Aug 16th

@techTFQ Жыл бұрын

It’s the ongoing class.. it started on 03rd Aug .. ends on 15th Sep.. you can still join it.. you will get access to the recordings for the missed sessions

@swamivivekananda-cyclonicm8781 Жыл бұрын

Happy Teachers Day:)

@techTFQ Жыл бұрын

Thank you so much :)

@freudsilver3097 7 ай бұрын

The recursive cte code is not working on hackerrank on this problem, i am not sure why. I used recursive cte in primary numbers question but its not working on this one.

@bk320929 Жыл бұрын

Thanks a lot for this scenario and solution. I was trying the first part of the problem by making recursive query in Oracle but it isn't working. Please show some light on this. Used query - with CTE (submission_date,hacker_id) as (select distinct submission_date,hacker_id from submissions where submission_date=(select min(submission_date) from submissions) union all select distinct CTE.submission_date,CTE.hacker_id from CTE,submissions S where CTE.hacker_id=S.hacker_id and S.submission_date=(select min(submission_date) from submissions where submission_date>CTE.submission_date) ) select * from CTE;

@johnbarrow8433 Жыл бұрын

It doesn't work in SQL Server either. The recursive part will not allow GROUP clauses (i.e. min()) in the SELECT. Also the DISTINCT clause is not allowed in the recursive part. PostgreSQL seems to be a lot more lenient and from the examples appears to rely on UNION to be applied to the recursive iteration as removes the second "79722 for 2016-03-02".

@johnbarrow8433 Жыл бұрын

I now have the solution working for MS SQL Server and have posted below the solution to the first part. As it doesn't rely on grouping, something similar should work in Oracle as well. Note that I have renamed some of the columns slightly as I prefer the camel case for the naming of columns. WITH dayBoundary (submissionDate, hackerId, dayId) AS ( SELECT submissionDate, hackerId, DENSE_RANK() OVER (ORDER BY submissionDate) FROM hacker.submission GROUP BY submissionDate, hackerId ), previousDay (submissionDate, hackerId, dayId) AS ( SELECT submissionDate, hackerId, dayId FROM dayBoundary WHERE dayId = 1 UNION ALL SELECT nextDay.submissionDate, nextDay.hackerId, nextDay.dayId FROM dayBoundary as nextDay INNER JOIN previousDay AS pd ON nextDay.hackerId = pd.hackerId WHERE nextDay.dayId = pd.dayId + 1 ) -- SELECT * FROM previousDay ORDER BY submissionDate, hackerId; SELECT submissionDate, COUNT(DISTINCT hackerId) FROM previousDay GROUP BY submissionDate This also illustrates an important point regarding the recursive queries, calling 'previousDay' in the recursive part ONLY returns the specific results from the previous iteration, NOT as I originally thought, the UNION of the previous days to date. The first DENSE_RANK query is there as the original query does not mention that the days will be consecutive (although they are in the example) otherwise could avoid the first CTE and use DATEADD(day, 1, pd.submissionDate) in the recursive WHERE clause.

@supriya1316 Жыл бұрын

Can you please upload more videos on hackerrank medium difficulty question

@techTFQ Жыл бұрын

I can but can you share with me some SQL problems which you would like me to solve. You can email me those problems. Since I do not personally use hackerrank, I am not aware of many hacker rank problems

@user-bf1se2ke2j 6 ай бұрын

Hello Thoufiq, Do we have other way to write instead of RECURSIVE function? Please let me know, how can we write it in alternative way to get same results.

@CassStevens Жыл бұрын

How is frank the correct output for day 5 when he didn't submit on day 2? or is the max number of submission not bound by the same 'every 1 consecutive days' requirement?

@chrisc.4144 10 ай бұрын

As you said, max submissions isn't related to hackers who submitted as least once every day.

@sbisbin8881 Жыл бұрын

Can you please do it in ms SQL

@johnbarrow8433 Жыл бұрын

Hi, see my reply to Bhargava Kamarapo above for MS SQL solution to the first part. Second half of solution in the video works ok.

@ameygoesgaming8793 3 ай бұрын

Also for the next query, SELECT sub_date, hacker_id FROM (Select sub_date, hacker_id, ROW_NUM() OVER(partition by sub_date, hacker_id order by hacker_id) FROM submissions) WHERE rn = 1

@bestcricketbowling9947 Жыл бұрын

Can u provide ur udemy class link?

@techTFQ Жыл бұрын

I do not have a Udemy course yet

@bestcricketbowling9947 Жыл бұрын

@@techTFQ plz make one,will be big help in becoming sql master😍

@techTFQ Жыл бұрын

Noted bro, will think abt it

@ragultamil9993 Жыл бұрын

Hi, In SQL, how to obtain a validated certificate

@cojocarudan8018 Жыл бұрын

It doesn't work queries with 'with' clause in hackerrank, and you must type subquery in subquery in subquery.. to work. Other people have this problem on platform.

@mm.veeresh5204 Жыл бұрын

What is difference between oracle sql, my sql, ms sql server, postgresql

@techTFQ Жыл бұрын

they are 4 different RDBMS

@mm.veeresh5204 Жыл бұрын

@@techTFQ which is the best

@techTFQ Жыл бұрын

All are good ..

@kolisettysasiram7732 Жыл бұрын

with recursive cte as (select submission_date, hacker_id from submissions where submission_date = (select min(submission_date) from submissions) union select s.submission_date, s.hacker_id from submissions s where s.hacker_id is in (select hacker_id from cte)) Is it crct ?

@nikhiljoshi3750 Жыл бұрын

Hi Taufiq how to get in touch with you, am giving senior level SQL interviews but not able to crack it, need your advice in my case

@sujaa1000 5 ай бұрын

In oracle SQL I am getting error "ORA-32044: cycle detected while executing recursive WITH query"

@Brownleaf-mi2wr 11 ай бұрын

Don’t u have hindi/Urdu channel ?? Please make it

@Explorecommonman-je6vu 7 ай бұрын

Hi, Do we have alternative way to write instead of RECURSIVE function? If yes please share me the code please.

@Explorecommonman-je6vu 7 ай бұрын

Would you please let me know that how write with out using RECURSIVE function with scenario.

@santoshmhamunkar2256 Жыл бұрын

please help me in clearing my doubt .... In Oracle , there is not keyword as "recursive" , right sir?

@Explorecommonman-je6vu 5 ай бұрын

Would you please anyone suggest or write SQL with out using RECURSIVE function with scenario.

@TheMemon40 Жыл бұрын

Hello i m planning for Exam Iz0-071 oracle sql certification, do you have any course that can help me pass that exams.. a complete course that can cover entire exam... thanx

@bnatara Жыл бұрын

I tried this in SQL Server and got stuck in the first part of the problem because "aggregation is not allowed in the recursive part of a recursive common table expression" where I was trying to get the min submission date

@GurrManagement 7 ай бұрын

Ran into this myself. Instead of the min, I used s.submission_date = dateadd(day,1,cte.submission_date) This should add one day every time it iterates over the base.

@neoistone 8 ай бұрын

While watching this video, I noticed an average of 3 to 4 hacker submissions each day. With my server configuration 128 cores, 256 GB of RAM, and 8TB of SSD storage, and considering I receive millions of daily visitors, could you provide an estimate of the time needed to retrieve this amount of data from MySQL?

@sameerkumardash5795 Жыл бұрын

Where are the materials to download and practice.. U always share them

@techTFQ Жыл бұрын

Haven’t shared this one yet.. probably will share this weekend and update you here

@sameerkumardash5795 Жыл бұрын

Thnx brother.. What u r doing is impressive.. Very inspiring personality.

@techTFQ Жыл бұрын

Thank you 🙏🏼

@alisharavika5929 2 ай бұрын

Hi Toufiq, I got error writing this query in Hackerrank, please help

@m.s.krishnamurthykitta Жыл бұрын

hi sir, i'm very much interested to learn SQL. but i'm unable to afford amount could you please help me to learn in free way...guide me in right direction

@Rajat-Thermite Жыл бұрын

Where can I make projects??

@techTFQ Жыл бұрын

dint get you

@Rajat-Thermite Жыл бұрын

@@techTFQ I mean that from where and how can I build some good Data Analytics projects to show them on my resume???

@MukeshSahu-mn2ps Жыл бұрын

your video is not clear, it is covering background. can you please correct it ?

@techTFQ Жыл бұрын

Please wait few mins, it’s still processing HD version.. should get fine in half an hour

@ameygoesgaming8793 3 ай бұрын

Can't we do...s.submission_date = cte.submission_date + 1 Simple avoiding a subquery

@roksig3823 Жыл бұрын

Input and output bit confusing. For submission date 2016-03-03 the unique hacker ID is 0 as all are already present in previous submission date’s For 2016-03-04 , unique hacker ID is 1 (44065) as other 3 are already present in previous submission date’s Can anyone clarify me.

@yaroslavpanych2067 Жыл бұрын

Having said that: "print total number of unique hackers that made at least 1 subm. each day" this is tacks that assumes output will be SINGLE NUMBER - number of hackers that was active each day. I do not see that as expected answer in resultsets. My conclusion is simple: do not waste time on such kinds of competitions. This is an example of tasks that you should NEVER EVER do. End of conversation.

@Ganeshay-996 Жыл бұрын

How Should i join..

@techTFQ Жыл бұрын

click on the link given in video description or just go to my website techtfq.com for all details

@yaroslavpanych2067 Жыл бұрын

"as he always says" yep, last video I watched, he even did not attempted to understand what was task. He jsut saw input and output data, made about a hundred assumptions about it and made made some solution... yeah...

@udaykumar-tb5kn Жыл бұрын

Can you tell me your course fee?

@sairohitkotla5405 Жыл бұрын

Hey Thoufiq, I hope you are doing well. Do you think we can use "count(distinct(hacker_id)" and" group by" to get total number of unique hackers per day.

@techTFQ Жыл бұрын

you also need to consider only the hackers who made submission each day