REAL SQL Interview Problem | Hierarchical data in SQL

In this video let us solve a real SQL interview problem. This problem is to find the hierarchy of employees.
We shall first understand the problem statement and the given data before solving the problem.
During the video, we shall also see how to debug a recursive SQL query. We shall try to come up with the different queries SQL generates during each iteration of the recursive SQL query. This will hopefully give you a complete understanding of how recursive SQL query works.
🔴 My Recommended courses 👇
✅ Learn complete SQL: learnsql.com/?ref=thoufiqmoha...
✅ Practice SQL Queries: www.stratascratch.com/?via=te...
✅ Learn Python: codebasics.io/courses/python-...
✅ Learn Power BI: codebasics.io/courses/power-b...
🔴 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
✅ Git and GitHub Tutorial:
• Git and GitHub
✅ Data Analytics Projects:
• Data Analytics Projects
THANK YOU,
Thoufiq

[@vinayakyerekar762]

Thank you sir. You are the only hope I have right now

[@amardeepkumar1267]

Superb! Explanation.Each and every minute is worth watching.

[@devendrabarasker4987]

How smoothly you clear the concept of recursive cte....great sir🙌🙌🔥🔥

[@KisaanTuber]

Thanks again Thoufiq for this wonderful explanation of recursive queries. Every video in your channel has something new to learn. Thanks a lot for sharing the knowledge.

[@saurabhsomkuwar1276]

Perfect explanation. Thanks Thoufiq!!

[@avi8016]

Excellent video sir💯
Thankyou so much for the consistent amazing content 🙏

[@AnilGola-dc7sl]

debugging iteration of recursive is amazing.

[@devarapallivamsi7064]

I don't think anyone else could do better in explaining this. crystal clear✅

[@shrinidhiha]

Excellent explanation on queries. This has helped a lot in my prob leg still having in SQL query

[@pugalg5151]

Step by step nice explanation 💗

[@hoperight5280]

Great explanation mate!

[@pavanareddy4536]

I’m your fan! Love how you explained your thought process and how to approach the problem, you are not just showing the solution but empowering others to analyze the problem and equipping them to think the solution themselves! I don’t think anyone has ever done this as effectively as you do 🙌 🙏 your passion for your work shows through, and it’s an inspiration to watch you! We are lucky to have you here!

[@techTFQ]

Thank you, Pavana 🙏
It's such an amazing feedback. loved reading it (even though I read it 2 months late 😬)
Really very glad you liked the video and my approach :)

[@pavankamalvadigi516]

Thanks again for the wonderful explanation.

[@AI-Health-posts]

great job Toiq bhai.

[@akshaysingh7962]

What an explanation! It was flawless!

[@Sreelalitha1903]

Superb explanation Sir,Clean and Crystal clear explanation.Thank you very much Sir.
👏

[@pawangadhave1548]

Thank you Sir. Excellent explanation on queries.

[@tatidutra]

Super useful and we'll explained video! Thank you

[@rajeevgaur5832]

Learnt something new today🎉

[@millennial_post]

Nicely explained...! Understood the concept of recursive in SQL easily..! Thank you so much.

[@techTFQ]

Super glad to hear that ☺️

[@kancharalaparameshwarreddy3837]

Thanks you sir thank you very much it is very useful to us

[@AnandKumar-rh1cv]

Great explanation

[@srinubathina7191]

explanation is very next level Thank You Sir

[@user-fc5vp8ij3n]

Hello sir, very useful concept. Kindly include with exit condition which is missing. Thank you.

[@bilalshahbazakmal2543]

Very useful 👍

[@sabithasaffar802]

What an explanation!!, i like your videos, but this is the best . Your teaching skill really amazing...Thank you

[@haleshab3216]

Hi Thoufiq,
Thanks for considering my question.
This question was asked to me in one of my interviews.
That time I didn't answer properly.
Now I got complete clarity on that recursive part of the query.
Once again thanks allot!!!!

[@techTFQ]

Thanks Halesha for sharing it with me ..
Much appreciated 🙏🏼

[@azad_pal]

Thank you Thoufiq... You are superb 🙏

[@ratonsito2836]

very well explained

[@bangaloreshydrohome5863]

Great video

[@devkilari9677]

Thank you !

[@futhedude4848]

best tutorial about Hierarchical data in SQL so far.

[@techTFQ]

Glad it helped

[@pavanareddy4536]

Great content! Thank you! Please do a video on sql tuning, explain plans and how to approach tuning queries for better performance! Any ideas on these are much appreciated!

[@Krishna48784]

Thank you so much for sharing 🙂

[@techTFQ]

Thanks for watching!

[@haekal2156]

Thank You sir

[@KeshariPiyush24]

Amazing

[@Kiranmai30]

Thank u soooooooo much sir

[@meerasoftech8445]

Nice

[@dravikrish7323]

Very complicated but nice explanation you need to watch 2 times minimum

[@malathyarumugam5074]

Great

[@Refreshment01]

Request:
Lateral joins & postgres functions.
Among the best tutorials are in this channel so thank you.

[@techTFQ]

Noted , will do soon

[@user-om2zc9ku3y]

Hi Thoufiq! Your videos and playlists on SQL have been very helpful. I went through many of your videos before appearing for an interview this week and it came in very handy. Thanks for these videos. I was asked a question in interview today with a service based startup that a table is given called Trips: Trip_id ,rider_id, city_id, trip_timestamp (actual data was not given). Question was Find users who have taken trips 5 consecutive days. I got an initial approach to use lag and then datediff to find difference between continuous trips but could not reach the final answer. Can you please help? Thanks in advance.

[@Kiranmai30]

I am new subscriber

[@bsukanya4091]

Sir may I know when is your next training session?

[@ragavik6342]

Hi, I completed my be 2013, ME 2015. Then I prepared government exams but not selected. Can I prepared these courses. I'm eligible for IT jobs

[@aanchalsrivastava8550]

Hi can u tell what is the shortcut key to display data of table in sql

[@iftikhar8508]

Try retrieving a tree in forest like what if there are multiple hierarchies? for example a table with data about countries and their president, prime minister and ministers. Here every country will have its own Hierarchy and hence more than one base cases.

[@girdhar3224]

good question good explanation good approach
only bad thing is the noob me

[@justkflesh]

May I know what workbench you use?

[@charupaul93]

Sir.....Can you please suggest that which SQL course we should opt for beginner to advance level learning from Learn.sql . there are showing too many different courses

[@fathimafarahna2633]

👍

[@avinashpratapsingh3875]

Can u please provide more problem query, so that we can practice more recursive query.

[@cococnk388]

I am beginning to understand recursive cte

[@user-ey9nr1em4w]

Can You make a video for sending email to specific recipient with summary attached on mail body use SQL Procedure. Please help me with this.

[@Pachaitamilanda]

👏👏👏

[@srisstea]

Make video for print type question in SQL

[@jhonsen9842]

Why aliasing emp_hierarchy same in both same in base query and recursive query.

[@anjali.8296]

I tried running query in snowflake .However its giving me wrong result .
with cte as(
select empid, empid manager from emp where empid=1
union all
select e.empid,c.empid manager from cte c join emp e on e.manager =c.manager
)
select * from cte ;

[@suhasjagadish4170]

hey @techTFQ how to solve this if we dont want to include the employee himself in the hierarchy list
(ex: if we dont want to include in 1 in 1 itself & 5,6,7,8,9)\

[@nakulimley6832]

with recursive cte as (
select eh2.emp_id, eh1.emp_id as emp_hierarchy from
Employee_Hierarchy as eh1 join Employee_Hierarchy as eh2 on eh2.emp_id =eh1.reporting_id
union
select cte.emp_id,eh.emp_id as emp_hierarchy
from cte join Employee_Hierarchy eh on cte.emp_hierarchy = eh.reporting_id
)select * from cte order by 1,2;

[@sagarbhadani1932]

What if employee id is not increasing order, will the output be same as I am getting output with few employee id missing 😮. Could u help"¿

[@ryuhayabusa3540]

So I'm learning SQL now, and i would like to believe I know more than just the basic. I believe I'm between basic and intermediate. i practice writing queries almost everyday.
Does anyone know what job/s I can get with what I know?
Please be realistic.
Thankyou

[@akhildevjr]

Hi

[@sagarbhadani1932]

I tried same way however I don’t get the all hierarchy.. please help

[@user-kf8vo1co8t]

can we solve this problem with the help of self join ?

[@motionofknowledge]

Which SQL editor are you using?

[@sohailkan17]

Postgre SQL

[@subodhthore6454]

-- Hierachy data SQl
create table hierData (
emp_id integer,
reporting_id integer
);
insert into hierData values
(1,null),
(2,1),
(3,1),
(4,2),
(5,2),
(6,3),
(7,3),
(8,4),
(9,4)
;
select * from hierData;
with recursive cte as(
select emp_id,emp_id as emp_hierarchy
from hierData
union all
select cte.emp_id, eh.emp_id as emp_hierarchy from cte JOIN hierData eh
on cte.emp_hierarchy = eh.reporting_id
)
select * from cte
order by emp_id;

[@shivameduri9443]

Hi Taufic, Please help me in hoe to get Age in the of 24 Years, 8 months, 10 days from Date of birth in sql. This question is asked in my previous interview. I have i verified all your videos for this type of scenario

[@nchou646]

if any company gave me this kind questions, I'll assume they don't like me

[@lavanyavijay6456]

I can't stop being amazed the way you teach complex queries in a simple way. And your channel is really a boon for person like me who wants to be skilled in SQL. Thank you so much for your examples and explanation.

[@ambartiwari953]

Hello Sir,
input string: hello, how are you? output characters: helo,wareyu? this is my question. please ans. me this qu.

[@ambartiwari953]

Sir, Plz give the solution

[@kirtipatil4629]

Hello sir plz help me to choose career. I hv done BE n mtech in CS. I have teaching experience around 7+ year. I am on break from last 4 yr for my kids. Now I want to restart my career as a devloper . I have strong knowledge of core Java n SQL queries. Which type of job profile best suited me?