Solving Complex SQL Query from HackerRank | SQL Interview Query

[@vishwanath-ts]

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

[@techTFQ]

Thank you :)

[@jatintiwari5555]

Eular problem is a SQL problem

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

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

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

[@hilarylomotey7051]

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

[@techTFQ]

Thank you so much :)

[@mateuszsmendowski2677]

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

[@techTFQ]

Glad you liked it

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

[@simranagichani4943]

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

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

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

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

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

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

[@data-dynamo-guy]

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

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

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

[@supriya1316]

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

[@techTFQ]

Glad I could help!

[@deepikavickraman505]

Very nice explanation and learnt recursive sql query 👌

[@1112electronics]

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

[@techTFQ]

Your welcome. happy to know it helps

[@ganeshgaitonde5971]

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

[@raktimmazumdar5794]

You are a lifesaver.

[@pablogalindo2089]

This was an incredible explanation! Thank you so much!

[@malateshhkulkarni4800]

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

[@techTFQ]

Thank you bro :)

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

[@VipinSingh-gz6pz]

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

[@bnatara]

You are doing a great job!!!

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

[@prernaaneja3941]

Thanks, this was very helpful.

[@DarkWebX-f]

when was the next batch started ?

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

[@tejaspatil3978]

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

[@muneess2979]

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

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

[@kancharalaparameshwarreddy3837]

Thanking you sir for more information

[@techTFQ]

Your welcome bro

[@freudsilver3097]

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.

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

[@santoshmhamunkar2256]

"WITH recursive" clause works in Oracle?

[@sateeshkumar-d9r]

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

[@enjoyfriends873]

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 😂

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

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

[@Explorecommonman-je6vu]

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.

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

[@sateeshkumar-d9r]

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.

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

[@fathimafarahna2633]

Fabulous ❤❤❤

[@techTFQ]

Thank you ☺️

[@homelessguy4143]

You are the best

[@techTFQ]

thank you buddy

[@muneess2979]

How to make bulk insert brother?

[@macdemon3486]

Brilliant!!

[@techTFQ]

Thank you 🙏🏼

[@CesarGarciaJara]

Thanks a lot for your video. 👍🏼

[@techTFQ]

Your welcome, hope you liked it

[@maheshsharma-zq2uc]

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

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

[@ShivamTiwari-on2kl]

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

[@avinash7003]

how to write in oracle?

[@santoshmhamunkar2256]

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

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

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

[@sateeshkumar-d9r]

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.

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

[@MyKidsFunChannel]

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

[@sujaa1000]

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

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

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

[@rohitmeshram4493]

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

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

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

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

[@SantanuRoy-oq2nv]

Hello Taufiq Sir, without recursive keyword can you try?

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

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

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

[@ameygoesgaming8793]

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

[@ragultamil9993]

Hi, In SQL, how to obtain a validated certificate

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

[@matts.4937]

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

[@Explorecommonman-je6vu]

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

[@Explorecommonman-je6vu]

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

[@neoistone]

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?

[@abbasmusawis]

Can you please help me to write some queries?

[@alisharavika5929]

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

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

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

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.

[@Explorecommonman-je6vu]

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

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

[@ameygoesgaming8793]

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

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

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

[@Brownleaf-mi2wr]

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

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

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

[@swamivivekananda-cyclonicm8781]

Happy Teachers Day:)

[@techTFQ]

Thank you so much :)

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

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

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

[@Ganeshay-09]

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]

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.

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

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

[@ramswaroop5019]

count(1) as no-of_unique_hackers how does it works

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