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:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer

[ankitbansal]

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;

[avi]

Loved the use of recursive cte to expand the positions table💯

Thankyou for bringing such videos🙏

[shubhamgoyal]

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

[manoj_mj]

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<=a.totalpost
)

select a.title, a.groups, a.payscale, coalesce(b.name, 'Vacant') from jp as a
left join cte as b
on b.rn = a.rn and b.position_id = a.id
order by a.id, b.rn;

[varunas]

Good one as always!

my attempt with tables variables and local variables on SQL server:


declare @itr1 int
set @itr1 = 1;
declare @itr2 int
set @itr2 = 1;
declare @t_r table (id int, title varchar(100), groups varchar(10), levels varchar(10));
declare @id int
set @id = 1;

while @itr1 <= (select COUNT(*) from #job_positions)
begin
while @itr2 <= (select totalpost from #job_positions where id = @id)
begin
insert into @t_r
select id, title, groups, levels from #job_positions
where id = @id

set @itr2 = @itr2 + 1;
end
set @id = @id + 1;
set @itr2 = 1
set @itr1 = @itr1 + 1;
end

select t.title, isnull(s1.name, 'VACANT POSITION') [name], t.levels
from (select ROW_NUMBER() over(partition by groups order by (select 1)) [rn], *
from @t_r) t
left join (select ROW_NUMBER() over(partition by position_id order by (select 1)) [rn], *
from #job_employees) s1
on t.id = s1.position_id and t.[rn] = s1.rn

[shreepadjoshi]

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 <=p.totalpost )
select coalesce(c2.name, 'Vacant'), c1.id from c1 left join (select *, row_number() over(partition by position_id order by id) as rn2 from job_employees) c2
on c1.rn1 = c2.rn2 and c1.id=c2.position_id order by c1.id, c2.name desc;

[ykirankumar]

with manager as(
select 2 as id, 'Manager' as title, 'B' as groups, 'l-14' as levels, 9000 as payscale, 1 as posts, 5 as totalpost from job_positions
union all
select id, title, groups, levels, payscale, posts+1 as posts, totalpost from manager where posts<totalpost),
AM as(
select 3 as id, 'Asst. Manager' as title, 'C' as groups, 'l-13' as levels, 8000 as payscale, 1 as posts, 10 as totalpost from job_positions
union all
select id, title, groups, levels, payscale, posts+1 as posts, totalpost from AM where posts<totalpost),
combine as(
select id, title, groups, levels, payscale, 1 as posts, totalpost from job_positions where id=1
union all
select distinct id, title, groups, levels, payscale, posts, totalpost from manager
union all
select distinct id, title, groups, levels, payscale, posts, totalpost from AM)

select title, groups, levels, payscale, coalesce(name, 'Vacant') as Employee_name
from combine c left join ( select *, ROW_NUMBER() over (partition by position_id order by id) as rn from job_employees) je
on c.posts=je.rn and je.position_id=c.id

[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 <= jp.totalpost)
--- rest is exactly as same as previous query.. just adjusted the references
Select rc.title, rc.groups, rc.levels, rc.payscale, coalesce(je.name, 'vacant') as employee_name from Total_Positions rc
left join job_employees je
on rc.id = je.position_id and rc.rn = je.id
order by rc.id, CASE
WHEN je.id IS NULL THEN 1
ELSE 0
END,
je.id;

[shivambansal]

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.

[anirbanbiswas]

A BIT DIFFERENT APPROACH OF RECURSION


with recursive cte as(select title, grups, labels, payscale, 1 as num from job_positions
all
select title, grups, labels, payscale, num+1 from cte where num< (select max(totalpost) from job_positions as jp where jp.grups=cte.grups group by grups))
select c.title, c.grups, c.labels, c.payscale,
case when j.name is null then 'vacant' else j.name end as new_name_column,
case when j.id is null then 'vacant' else title end as new_title
from cte as c left join job_employees as j on c.num=j.position_id
order by grups

[apurvasaraf]

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<totalpost)
, ct as(select * from cte )


, c as (select *, ROW_NUMBER()over(partition by position_id order by position_id) as r
from job_employees )

select *, coalesce(name, 'v') as name from ct left join c on
position_id=ct.id and r=t

[sabh]

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

[abhayable]

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

[apurvasaraf]

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<=totalpost)
, cte1 as (select *, ROW_NUMBER() over(partition by position_id order by position_id) as rn
from job_employees)

select cte.id, title, groups, levels, payscale, totalpost, coalesce(name, 'vacant') as name
from cte left join cte1 e on cte.id=e.position_id and tt=rn
order by title

[AbhishekKumar-gfdb]

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

[tanishnamdev]

Thanks sir for such a wonderful concept ❤

[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

[uttamx]

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

[gourav]

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 🙏