Solving a Advanced SQL Interview Problem | Advanced SQL Tutorial

In this video we will discuss a advanced sql interview problem using 2 methods:
script:
create table job_positions (id int,
title varchar(100),
groups varchar(10),
levels varchar(10),
payscale int,
totalpost int );
insert into job_positions values (1, 'General manager', 'A', 'l-15', 10000, 1);
insert into job_positions values (2, 'Manager', 'B', 'l-14', 9000, 5);
insert into job_positions values (3, 'Asst. Manager', 'C', 'l-13', 8000, 10);
create table job_employees ( id int,
name varchar(100),
position_id int
);
insert into job_employees values (1, 'John Smith', 1);
insert into job_employees values (2, 'Jane Doe', 2);
insert into job_employees values (3, 'Michael Brown', 2);
insert into job_employees values (4, 'Emily Johnson', 2);
insert into job_employees values (5, 'William Lee', 3);
insert into job_employees values (6, 'Jessica Clark', 3);
insert into job_employees values (7, 'Christopher Harris', 3);
insert into job_employees values (8, 'Olivia Wilson', 3);
insert into job_employees values (9, 'Daniel Martinez', 3);
insert into job_employees values (10, 'Sophia Miller', 3)
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

[@ankitbansal6]

Please like the video to increase its reach. Thank you 🙏

[@Datapassenger_prashant]

I am so grateful that I found your channel. there was a time i wasnt able to write even a simple query of join or groups. but now solving a question and buliding problem solving approach to solve a statement has immensly improved. for this question my approach was just to apply recursive cte. however, used cases of r_ctes are less so after bit of hit & trials created the perfect solution, without even watching the video. now its just that need to check whether my sol was aam or mentos 😂 here is my sdolution anyways: with r_cte as ( Select id, title, groups, levels, payscale , totalpost, 1 as cnt from job_positions union all Select id, title, groups, levels, payscale , totalpost, cnt + 1 from r_cte where cnt < totalpost ) Select rc.title, rc.groups, rc.levels, rc.payscale, coalesce(je.name,'vacant') as employee_name from r_cte rc left join job_employees je on rc.id = je.position_id and rc.cnt = je.id order by rc.id,CASE WHEN je.id IS NULL THEN 1 ELSE 0 END, je.id;

[@shivambansal3560]

00:04 Solving an advanced SQL interview problem with two methods. 02:42 Create output with rows equal to total positions available 05:02 Using recursive CTE to generate the required number of rows for each position in the job positions table 07:43 Understanding the number of rows for different positions and generating row numbers for joining 10:29 Perform left join to handle vacant positions 13:27 Generate a running number table based on the maximum number of total posts in a table. 15:46 There are multiple ways to generate a number from one till the maximum number of total posts in a table. 18:11 Using a unique primary key as a row number can limit the number of rows in a query 20:58 Using a trick with non-equijoins and recursive CT, we achieved the same output with the given SQL problem.

[@avi8016]

Loved the use of recursive cte to expand the positions table💯 Thankyou for bringing such videos🙏

[@ankitbansal6]

My pleasure 😊

[@vikramrathore8512]

You’re Best Ankit

[@Datapassenger_prashant]

Hi Again, I just completd watching the mentos part but felt like no, we shouldnot consider any other table from database rather create a blank table using recursive cte, that was much better, I can understand, there are less lines written in your mentos, but for visual appearance, i found the creating a blank table with running numbers. with cte1 as ( select max(totalpost) as Maxt from job_positions), cte as ( Select 1 as rn union all select rn + 1 from cte where rn < (select maxt from cte1) ), Total_Positions as ( Select c.rn, jp.* from cte c inner join job_positions jp on c.rn

[@shreepadjoshi5142]

My approach: with c1 as (select p.* ,row_number() over(partition by title order by p.id) as rn1 from job_positions p join job_employees e on e.id

[@AbhishekKumar-gf4db]

Such a amazing video.very help full to prepare interview. Thanks lot sir.❤

[@ankitbansal6]

Most welcome

[@shubhamgoyal5227]

Bhai aap toh SQL ghol ke pee gye hoo...kya dimaag hae bhai aapka 👌👏

[@ankitbansal6]

Bhai 12 saal se SQL likh rha hoon 😊

[@gourav1343]

@@ankitbansal6 And the same thing you are trying to teach us, We are glad with whatever you are doing to teach us.

[@apurvasaraf5828]

with cte as( select id,title,groups,levels,payscale,totalpost ,1 as tt from job_positions union all select id,title,groups,levels,payscale,totalpost , tt+1 as tt from cte where tt+1

[@TheCraigie007]

Awesome. Thanks Ankit.

[@ankitbansal6]

Welcome!

[@abhay88able]

Why order table... Evn u dint mention abt it in starting.

[@tanishnamdev1183]

Thanks sir for such a wonderful concept ❤

[@ankitbansal6]

Most welcome😊

[@apurvasaraf5828]

with cte as ( select id,title, payscale,totalpost,1 as t from job_positions union all select id,title, payscale,totalpost, t+1 as t from cte where t

[@manoj_mj0715]

Similar as like you shown, Just taken running no's as you told. with cte as( select name, position_id, row_number() over(order by a.id) as rn from job_employees as a join job_positions as b on a.position_id = b.id), jp as (select a.id, a.title, a.groups, a.payscale, a.levels, b.rn from job_positions as a join cte as b on b.rn

[@parth_pm16]

you're calculating "running no's " based on job_employees' id not MAX of job_positions's "totalpost" there is coincidence that total job_employees' id is 10 and MAX of job_positions's totalpost is also 10. FYI your query generate correct output for this data only.

[@uttamthakur2016]

I understand first method of doing but not able to uderstand 2nd method.

[@100sabh]

Only ..God knows how your recursive CTE is working without joining on Title .. Very strange . But I admire your work and knowledge in SQL

[@fog2640]

finished watching

[@RajeshwarUma]

Hi Ankit your way of teaching very good, i seeing each and every video, way could you help me on the suqquery and CTE which situation we need to use confusing little bit about that if possible do one good video it will help for so many users

[@ankitbansal6]

Sure

[@gourav1343]

Hi Ankit Q1. I was just wondering about your zero to hero SQL course could you please help me with : Like if one is able to solve all these 57 videos on his own, for that person the course prepared by you make any sense ? Since you have already covered most of the topics in these 57 videos. I believe the course is for one who wish to learn SQL from scratch. And those who already knows a descent level of SQL can enhance there skills by practicing complex SQL queries Q2. please also mention the average video length of Namaste Python (Zero to Hero) Please guide 🙏

[@ankitbansal6]

Yes that's for those who want to learn from scratch. Python each class 1.5 hours. Around 15 classes

[@akkys97]

Sir Atleast recorded lectures ka price kam kar dijiye

[@ankitbansal6]

It's 2k only. What are you expecting?

[@akkys97]

@@ankitbansal6 i understand sir but mein or meri tarah bahut log hai jo 2k bhi afford nahi kar paate but learning mein bahut interest hai

[@ankitbansal6]

@@akkys97 send me email on [e-mail]

[@indergaming3053]

With r_cte as ( select id ,title ,groups ,levels, payscale,totalpost from job_positions union all select id,title,groups,levels,payscale, totalpost-1 from r_cte where r_cte.totalpost>1 ), cte2 as ( select *, row_number () over (partition by position_Id order by (select null)) as ranking from job_employees) select r_cte.title, r_cte.groups,r_cte.levels,r_cte.payscale, coalesce (cte2.name, 'vacant') as employee_name from r_cte left join cte2 on r_cte.id=cte2.position_ID And r_cte.totalpost=cte2.ranking order by r_cte.groups asc , r_cte.totalpost asc rate this query out of 10 please bhai atleast reply