Solving a Real SQL Interview Problem | Job Vacancies SQL Query

In this video, we are Solving a Real SQL Interview Problem about displaying Job Vacancies using an SQL Query.

Treat this SQL problem as a challenge, watch the first 4 mins of the video to understand the problem statement and my approach to solving this problem. You can then pause the video and go to my blog (link below) to download the dataset and create them in your database and then try to solve the problem yourself.

If your solution is the same as mine, leave a comment below as “SOLVED! Same Solution”, if your solution is different from mine then leave a comment as “SOLVED! Different Solution” and then leave your solution in the comment. However, if you are unable to solve the problem then let me know in the comment what was the challenge you faced.

Download the dataset, scripts, and solution from my blog:

JOIN my Python Bootcamp:

Thank you,
Thoufiq

[raghulmohan]

You are master in sql we need more problems like this

[vikrantheswar]

I really like your explanation. I can see your teaching passion in it. Continue sharing knowledge 👍🙌👏

[Mayank-jwyy]

this video is absolute gem to watch, no better person available on internet to teach you SQL.

[decimus]

I did it using the recursive method. Don't worry about column names in recursion. I just needed to understand recursion.

with t1 as
(select name, position_id,
ROW_NUMBER() over(partition by position_id order by id) as rn
from job_employees
)

, job_cte as
(select id as dd, 1 as cnt, title, groups, levels, payscale
from job_positions

union all

select b.id, cnt+1, b.title, b.groups, b.levels, b.payscale
from job_positions b
inner join job_cte a on a.dd = b.id and cnt <b.totalpost)

select dd, cnt, title, groups, levels, payscale,
case when b.name is null then 'Vacant' ELSE b.name end as EMPLOYEE_NAME
from job_cte a
left join t1 b on a.dd = b.position_id and a.cnt = b.rn
order by dd, cnt

[dantushankar]

My solution which is little similar:
with cte as (select a.title as t, a.groups as g, a.levels as l, a.payscale as p, b.name as n from (select jp.*, row_number() over (partition by id order by id) as r1 from job_positions jp, generate_series(1, jp.totalpost)) a left join
(select *, row_number() over (partition by position_id order by id) as r2 from job_employees) b on a.r1 = b.r2 and a.id = b.position_id)

select t, g, l, p, case when n is null then 'Vacant' else n end as Emp_Name from cte;

[babahussain]

First of all thanks for uploading this type of SQL video and Please upload more videos like this and if you have time please make live videos as well

[yugendarsairam]

Hey Toufik, your lectures are super helpful in understanding the concepts in a much better way. Appreciate your inputs here. Could you please make a video on transactions and indexes, that would really help a lot to all our folks.

[Tusharchitrakar]

I used recursive cte to stack up the job postings (similar to ungrouping) but thanks for this alternate solution

[kaivalyapatkar]

I request you to please start a Interview Problems Series! It would help a lot!

[ejjirotusrinivas]

with cte(lvl, id, title, groups, levels, payscale, totalpost) as (select 1 as lvl, id, title, groups, levels, payscale, totalpost
from job_positions union all select lvl+1 as lvl, id, title, groups, levels, payscale, totalpost
from cte where lvl <totalpost),
cte1 as ( select row_number() over(partition by position_id order by name ) rw, j.* from job_employees j)
select Title, groups, levels, payscale, coalesce(name, 'Vacant') as employee_name from cte c left join cte1 cc on c.lvl=cc.rw and c.id =cc.position_id
order by groups, lvl ;

[Manzur.A]

This YouTuber is the best and will go far more than other YouTubers.

[shrinath]

Please upload more videos like this 👌👌

[RafidShahriar-hm]

Mine solution was same as yours. Except I use recursive CTE to generate series in MS SQL. 😀

[viktorponomarev]

The same solution pop-uped in my head. Definitely there are other ways to solve the problem, but the shown solution is clever and readable and I cant figure out the better one.

[integer]

My version, But a bit unreadable(

with cte as (select level rownb from dual connect by level <= (select max(totalpost) from job_positions))

select p.title, p.groups, p.levels, p.payscale, nvl(e.name, 'Vacant') Employee_name
from (select * from job_positions p cross join cte c where c.rownb <=p.totalpost order by groups, rownb) p,
(select row_number() over(partition by e.position_id order by e.id) as rownb, e.* from job_employees e ) e
where p.id= e.position_id(+) and p.rownb = e.rownb(+)

[martinberger]

You rock, bro! Haven't seen someone that keen on SQL than you!

[archisingh]

Sir.... Why I am not getting idea to write query like you did in this video.... I know each functions but still I am. Not able to write complex query. Please give me some idea how can I improve atleast 40% of your SQL knowledge.

[satyaprakashdash]

with recursive cte as (
select id, title, groups, levels, payscale, totalpost from job_positions
union
select id, title, groups, levels, payscale, totalpost-1 as totalpost from cte where totalpost >1
), cte2 as (select id, name, position_id, row_number() over(partition by position_id) as rn from job_employees)
, cte3 as(
select a.*, b.* from cte a
left join cte2 b on a.id=b.position_id and a.totalpost=b.rn)
select title, groups, levels, payscale, case when name is not null then name else 'Vacant' end as employeename from cte3

[felipemv]

Parabéns pelo ótimo vídeo - ótima explicação e detalhamento de cada passo. Muito bom!!!
Congratulations for the great video - great explanation and detailed step by step. Very good!!!

[venkateswararaodevisetti]

Hi Sir,
I have answered by pausing your video
Below is my query, not sure whether u used same way or not


with recursive job_positions_data as
(
select id, title, grp, levels, payscale, 1 as row_id, totalpost from job_positions
union
select id, title, grp, levels, payscale, row_id + 1 as row_id, totalpost from job_positions_data
where row_id<totalpost
)
select jpd.title, jpd.grp, jpd.levels, jpd.payscale, coalesce(emp.name, 'Vacant') as emp_name
from job_positions_data jpd left join (
select id, name, position_id, row_number() over(partition by position_id order by id ) row_id
from job_employees) emp
on jpd.row_id=emp.row_id and jpd.id = emp.position_id
order by jpd.grp, jpd.row_id




But, I learned recursive queries by watching u r videos only

Thanks a lot sir :)

Plz post more this kind of questions
And plz post some query optimization techniques as well


Thank a lot once again :)