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
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 ☺️
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.
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
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
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
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)
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.
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 😂
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.
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.
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
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;
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".
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.
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.
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
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.
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.
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?
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 ?
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.
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?
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.
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?
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
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.
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
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
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.
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
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
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.
"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...
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
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.
The way you explained everything in detail I subscribed immediately.
This is how SQL need to be taught
Thank you :)
Eular problem is a SQL problem
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.
very glad to hear that :)
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
I have seen many solution of this question. But finally my search comes to an end i understand each and every thing crystal clear.
Great explanation and awesome practical example that distinctly illustrates the purpose of recursive sql queries!
Glad you liked it
The best SQL teacher ever, awesome man , u rock too much
Thank you so much :)
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
Your welcome Firdose 🙏🏼
Sunke acha laga 👍
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 ☺️
Good suggestion, ill consider it
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..
Glad to hear that Dheeraj
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.
Thank you :)
Very glad to read this
Love your these kind of videos where you show us how to solve a complex sql query👌Thanks a lot for such amazing videos🙌🏻🙏
Glad you like it bro 😍
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
Another awesome content from Tofig,
Thank you so much. Clicked too much the liked button and it’s broken now. 😂😂😂😂
haha Thank you bro :)
Wow... You made this video... I was facing problem with this question!
Glad I could help!
Thank you so much.. I was looking for this from some time now. Your style of explanation is awesome. Keep up the good work.
I am very grateful for what you did in this video. Wish your channel grow more !
Thanks for the great video on SQL you always producing.... awesome..please keep it up.
Your welcome. happy to know it helps
Very nice explanation and learnt recursive sql query 👌
Thoufiq I just wanted to say hi, and congratulations……SO close to 100K, way to go, friend!!
Thank you Laura ☺️
Very happy to see your comment.. I hope things are going good for you.
Thank you sir..i don't know if anyone can better explain this..👌
Thank you bro :)
This was an incredible explanation! Thank you so much!
thank you so much sir 🥰😍 ............it Helped me a lot to understand concepts of SQL more deeply🌹🌷🌼
You are a lifesaver.
You are doing a great job!!!
Just loved it the way you explain....Plz make a video on REGEXP in Oracle DB ... Thanks Taufeeq
glad you liked it and noted on the request
Interesting problem and good example on how to use recursion.
Can you please share the query to create the tables required for this problem?
Thank you :) and will share after few days
@@techTFQ, can you please share the query to create tables for this problem?
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
Thanks, this was very helpful.
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
Thanking you sir for more information
Your welcome bro
Brilliant!!
Thank you 🙏🏼
Hi Thoufiq Bhai, Please explain and make a other video to write instead of RECURSIVE function? I mean without using Recursive function.
You are the best
thank you buddy
Hi sir, what ide are you using postgreSql editor, thank you for the video
I use PgAdmin
@@techTFQ Thanks for the update
Thanks a lot for your video. 👍🏼
Your welcome, hope you liked it
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)
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.
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
Hey Taufiq, you were not supposed to use CTE for this particular problem. You have to use only subqueries and joins
Fabulous ❤❤❤
Thank you ☺️
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 😂
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.
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.
@@techTFQ Thanks thoufig for the clarification.. Now am cleared
Hi Toufiq, can you make a video on the current industry salary for data analyst acc to exp in India?
may be in the future bro.. salary varies from company to company so difficult to predict
@@techTFQ sure toufiq.
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.
probably early next year
@@techTFQ Next Year /month ?😇
Bro there is a youtube Telugu channel which focuses on it manager vs employee differences which uses your name as scolding
Haha is it which channel n video
@@techTFQ Sai Kiran yediya
hey, plz make the video on youtube scraping data and store it in sql using python and beautifulsoup
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
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
Let me consider it
when was the next batch started ?
Hi toufeeq,
Can you kindly tell me from where I can learn regular expressions for oracle sql platform
I will probably make a video about it soon
Hi Taufiq, can you make a course on solving SQL challenges or solving interview questions on lighthall, udemy, or anywhere else?
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;
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".
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.
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.
How to use if condition in with clause along with recursive.. Can u give me an example
Hi Taufiq
Can you please let me know when is your next sql batch will start?
Probably early next year.. not finalized yet
Just want to clarify, I saw one upcoming class on Aug 16th
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
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.
What is difference between oracle sql, my sql, ms sql server, postgresql
they are 4 different RDBMS
@@techTFQ which is the best
All are good ..
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.
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?
As you said, max submissions isn't related to hackers who submitted as least once every day.
Hi any idea how to get minimum and maximum value together for particular month
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 ?
Where are the materials to download and practice.. U always share them
Haven’t shared this one yet.. probably will share this weekend and update you here
Thnx brother.. What u r doing is impressive.. Very inspiring personality.
Thank you 🙏🏼
How to make bulk insert brother?
"WITH recursive" clause works in Oracle?
Hi, Do we have alternative way to write instead of RECURSIVE function? If yes please share me the code please.
Would you please let me know that how write with out using RECURSIVE function with scenario.
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.
Can u provide ur udemy class link?
I do not have a Udemy course yet
@@techTFQ plz make one,will be big help in becoming sql master😍
Noted bro, will think abt it
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?
Hello sir, Is it mandatory to practice tricky query for data analysis role?
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.
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?
In oracle SQL I am getting error "ORA-32044: cycle detected while executing recursive WITH query"
please help me in clearing my doubt .... In Oracle , there is not keyword as "recursive" , right sir?
Hi sir, to attend for interview your sql videos enough or not
Hello Taufiq Sir, without recursive keyword can you try?
Hi,
In SQL, how to obtain a validated certificate
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
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.
how to write in oracle?
Can you explain the count(1) meaning ??
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
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
your video is not clear, it is covering background. can you please correct it ?
Please wait few mins, it’s still processing HD version.. should get fine in half an hour
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.
Can you please upload more videos on hackerrank medium difficulty question
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
Hi Toufiq, I got error writing this query in Hackerrank, please help
Can you please help me to write some queries?
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
Happy Teachers Day:)
Thank you so much :)
Hi guys,
can anybody share the query that creates the tables required for this exercice so we can test by ourselves?
Thanks in advance!
Would you please anyone suggest or write SQL with out using RECURSIVE function with scenario.
Can you please do it in ms SQL
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.
Don’t u have hindi/Urdu channel ??
Please make it
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
Where can I make projects??
dint get you
@@techTFQ I mean that from where and how can I build some good Data Analytics projects to show them on my resume???
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.
Can't we do...s.submission_date = cte.submission_date + 1
Simple avoiding a subquery
"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...
How Should i join..
click on the link given in video description or just go to my website techtfq.com for all details
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
Can you tell me your course fee?
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.
you also need to consider only the hackers who made submission each day
count(1) as no-of_unique_hackers how does it works