Solving a Real SQL Interview Problem | Job Vacancies SQL Query

Рет қаралды 50,741

Күн бұрын

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:
techtfq.com/blog/solving-a-re...
JOIN my Python Bootcamp:
techtfq.graphy.com/courses/Py...
Thank you,
Thoufiq

Пікірлер: 150

@raghulmohan5937 10 ай бұрын

You are master in sql we need more problems like this

@yugendarsairam9688 10 ай бұрын

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.

@Manzur.A 10 ай бұрын

This KZfaqr is the best and will go far more than other KZfaqrs.

@viktorponomarev4048 10 ай бұрын

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.

@aadamhaydin5895 10 ай бұрын

Really loved the way you simplied the problem.

@felipemv13 10 ай бұрын

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!!!

@Tusharchitrakar 10 ай бұрын

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

@vikrantheswar7685 10 ай бұрын

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

@techTFQ 10 ай бұрын

Thanks a lot :)

@martinberger365 4 ай бұрын

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

@mikarshdataanalyst4086 10 ай бұрын

Great video Thoufiq!

@kaivalyapatkar9288 10 ай бұрын

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

@ejjirotusrinivas8376 10 ай бұрын

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

@monicabn3067 10 ай бұрын

Best one sir❤ please keep educating and challenging us🎉

@meenarnath9091 3 ай бұрын

This is really a service. Thank you so much Sir.

@babahussain786 10 ай бұрын

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

@techTFQ 10 ай бұрын

Live streaming to solve problems.. good suggestion, will consider it

@shiju94 10 ай бұрын

Wonderful explanation sir.

@MdNisarAhmed-de7vd 10 ай бұрын

Your content is so good that i like your video before even watching ❤️

@paularakowska6060 10 ай бұрын

Amazing, thanks!;)

@shaikhanuman8012 10 ай бұрын

Clear cut explanation tq sir

@user-pm2sz7lc6q 4 ай бұрын

This dude is a genius 🎉

@priyankasuryavanshi6888 9 ай бұрын

Your explanation is on another level, Thanks!!! It's really helpful

@techTFQ 7 ай бұрын

You're very welcome!

@dantushankar4470 Ай бұрын

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;

@nieja5920 10 ай бұрын

Thanks!! learnt something new today, can you upload a version of this using recursive sql? Thanks!

@Canadavlogsbyanu 10 ай бұрын

I really loved your videos❤

@decimus1447 5 ай бұрын

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

@ganeshv791 10 ай бұрын

Vera level

@huzaifahshah3841 10 ай бұрын

Yes sir make more Videos solved the problems

@dilipinamdar5523 10 ай бұрын

Thanks for uploading this type of SQL video..I am unable to use generate_series function in mysql ..please suggest!!!!

@Kiranmai30 10 ай бұрын

Thanks a lot

@devarakondasankararao3802 9 ай бұрын

Great video

@sanmeetsingh8255 10 ай бұрын

Great explanation so I also want to send you an interview question related to SQL. Can you please tell me where can I send that question?

@nandinimnair9915 10 ай бұрын

Hi, Thankyou for yet another wonderful video.can you please let me know which oracle certification a person with 12 yrs of experience in plsql should take..?

@kirankumarbm7391 10 ай бұрын

Superb, Thanks

@techTFQ 10 ай бұрын

Your welcome 🙏

@satyaprakashdash459 9 күн бұрын

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

@sammail96 4 ай бұрын

This is very interesting problem

@Ibad_gamer937 10 ай бұрын

Amazing

@edwardmensah4110 10 ай бұрын

Thanks. Please make more of this video

@techTFQ 10 ай бұрын

Thank you will do

@rajasekhar8261 10 ай бұрын

Any update on your oracle course learning videos. You mentioned that you are going to create a platform and place your videos there?

@shrinath29 10 ай бұрын

Please upload more videos like this 👌👌

@ryuhayabusa3540 10 ай бұрын

I love theses videos

@techTFQ 10 ай бұрын

Thank you guys 😍

@armaanp5585 10 ай бұрын

Indeed these are good videos. I found the below interesting SQL interview task for Data Analyst. This may help. kzfaq.info/get/bejne/nZOSf8ic25jPmok.html

@integer4531 10 ай бұрын

My version, But a bit unreadable( with cte as (select level rownb from dual connect by level

@techTFQ 10 ай бұрын

nice man, well done

@samratsingha8515 10 ай бұрын

Super

@ajkuchipudi893 10 ай бұрын

pls upload more like this

@user-dv7pt6no6c 10 ай бұрын

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

@anjankumaro7530 10 ай бұрын

Can you share me the code of CTE. your used.

@sammail96 4 ай бұрын

@@anjankumaro7530I don't know exactly his solution but let try this oracle sql query: WITH CTE AS ( SELECT E.*, ROW_NUMBER() OVER (PARTITION BY GROUPS ORDER BY GROUPS) AS RN FROM ( SELECT * FROM JOB_EMPLOYEES J JOIN JOB_POSITIONS JP ON J.POSITION_ID = JP.ID) E), CTE1 AS ( SELECT F.*, ROW_NUMBER() OVER (PARTITION BY GROUPS ORDER BY GROUPS) AS RN FROM ( SELECT ID, TITLE, GROUPS, LEVELS, PAYSCALE FROM job_positions CONNECT BY PRIOR ID = ID AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL --if not used then error: CONNECT BY LOOP in user data AND LEVEL

@olaidealafia1965 8 ай бұрын

Very helpful and detailed

@techTFQ 7 ай бұрын

Glad you liked it

@kashmirshadows8150 10 ай бұрын

Thanks you Thoufiq Any update on the Udemy course ??

@siddharthshivdasani 10 ай бұрын

Hey! What if we took row number instead of generate series for job_positions table n gonna partition by title which had the value of manager n all…. It would also give same output na??

@murapalasravani2519 9 ай бұрын

SIR, we can also join job_positions and employee by only using position_id as common column right? then what is the need of using row number window function there?

@bijumohan9460 10 ай бұрын

Hi Taufiq can you pls do a video on using connect by clause in oracle?

@csekhar4155 10 ай бұрын

Your teaching is awesome. When are you starting sql boot camp for new batch?

@techTFQ 10 ай бұрын

I will release a recorded course next month

@richardtorrenueva5512 10 ай бұрын

Bootcamp like this will join

@RaviYadav-cx2pb 10 ай бұрын

@@techTFQ really needed SQL bootcamp on graphy

@vijayroyal12 9 ай бұрын

Please upload more scenarios anna

@santhoshKumar-zf7ox 10 ай бұрын

Hi, I have a question on dates, I need to get date of 2 or 3rd occurence of Monday in a month, please make a video or SQL query..

@abhijathprasanth8542 9 ай бұрын

Hi, can you upload the same in oracle db?

@archisingh9892 8 ай бұрын

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.

@techTFQ 7 ай бұрын

Practice. All the best

@beks5755 10 ай бұрын

@techTFQ I assume this task is not from a junior position interview but more like mid or senior-level? Am I right?

@funshojegsin5431 6 ай бұрын

pls which sql database are you using because I'm using azure?

@sajidulahmed7972 9 ай бұрын

Thanks for the problem! May I know, if this is the question for an experienced person who’s looking for a Data role or even for freshers, we gonna get such questions? And if it’s for an experienced guy, then may I know the count of exp? If any one can help me as I am trying to change my domain into Data roles.

@avinashpratapsingh3875 10 ай бұрын

Hi Thoufiq thanks for this question, please make video for query asked in MAANG companies.

@techTFQ 10 ай бұрын

Noted, will do

@avinashpratapsingh3875 10 ай бұрын

@@techTFQ, how to use generate_series in oracle db.

@chibumbamohammed5006 10 ай бұрын

I have liked this video it so challenging.

@techTFQ 10 ай бұрын

thank you

@swapnildhivar930 10 ай бұрын

I solved using Cte ' s only and another solution is using recursive cte ( i used MySQL)

@techTFQ 10 ай бұрын

Awesome 👌 well done

@nivisworld1513 10 ай бұрын

Sir, please post all the sql classes please sir..........i am trying for job but so many qns on sql they are asking.....iam very poor in sql....but while listening your classes i clearly understand the concept ......plz....if i want talk about course personally ...how cam i contact you sir.....plz rply me sir....

@user-mm6sp1tc4p 9 ай бұрын

hello, would you please solve some interviews like these are but for python?

@ibrahimidris7741 10 ай бұрын

Select Emp_name, title, Group, Level, payscal From Emp Where Position_ID = ID,

@Krishna48784 10 ай бұрын

I have one small question, in postgresql how can we return multiple results sets with different column size , Ms SQL server returned this , but in postgresql is it possible. Give answer please

@jithin_raj 4 ай бұрын

with recursive cte_rec as (select id,title,groups,levels,payscale,totalpost, 1 as level from job_positions union select id,title,groups,levels,payscale,totalpost,level+1 from cte_rec where level

@gobindaroy7811 10 ай бұрын

Please upload more such videos

@techTFQ 10 ай бұрын

noted will do

@TheDataMagic_x 7 ай бұрын

You have other options to solve because sir my system does not accept the generate_ series' function

@rishabhgupta3826 10 ай бұрын

Hi Sir, Require one help related to mysql query where a table having data into month and yearwise only with single or multiple value so want to extract data where if same month and year repeated twice with different value then column M1 return M1='R' and again M1='B' and if same month and year repeated only once say Aug2023 then M2='R' and again M2=' ' and if again another same month and year repeated only once I.e sept2023 then M3='B' and M3=' ' respectively into mysql, kindly help me to share the way or query through which I can make on my db and can extract the data

@abdullahsheikh5459 3 ай бұрын

How do I solve this query in mysql DB as generate_series() is not present in mysql ??

@allrounderashok4712 11 күн бұрын

SQL SERVER SOLUSTION with cte as ( select 1 n, id,title,groups, level, payscale,totalpost from postition union all select n+1,id,title,groups,level,payscale,totalpost from cte where n

@venkateswararaodevisetti8684 10 ай бұрын

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

@techTFQ 10 ай бұрын

Good job buddy

@venkateswararaodevisetti8684 10 ай бұрын

@@techTFQ Thanks Bro

@shivinmehta7368 6 ай бұрын

my solution with recursive cte as ( select id,title,groups,levels,payscale,totalpost,1 as n from job_positions union select id,title,groups,levels,payscale,totalpost,n+1 as n from cte where totalpost>n ) select cte.title,cte.groups,cte.levels,cte.payscale,cte.totalpost,coalesce(e.name,'vacant') from cte left join (select *,row_number() over(partition by position_id order by id) as rn from job_employees) e on cte.id=e.position_id and cte.n=e.rn order by cte.id

@RaviYadav-cx2pb 10 ай бұрын

Can you please launch new course beginners to pro sql with project on graphy

@viveks4935 10 ай бұрын

How to solve it in mysql and oracle db?

@fathimafarahna2633 10 ай бұрын

👌👌👌👌

@techTFQ 10 ай бұрын

😍

@lovebites4098 8 ай бұрын

can u please find how to do the same in mysql

@charlesarome5792 10 ай бұрын

Good content, u are too fast in ur delivery, please ,if u can speak slowly so we can grasp

@sreenivasulukanchi1612 6 ай бұрын

In MS SQL Server, there is no Generate_Series.

@manickavasuki2375 10 ай бұрын

Hi Thoufiq I want to learn sql from basic to advance when are you launching ur course in Udemy

@YasinKAYA-do4ek 10 ай бұрын

@techTFQ 10 ай бұрын

I will launch the course soon and it will be on my own platform .. working on it currently

@YasinKAYA-do4ek 10 ай бұрын

@@techTFQ so no udemy then. Will there be certificate? I wanna show of that I learned SQL in my cv. Otherwise people don't take it seriously when I say I have been managing ERP database for years. Apperantly some paper makes more impact than years of developing ERP software on my own.

@newenglandnomad9405 10 ай бұрын

Would this qualify as intermediate or advanced? I can't get generate series to work on mysql 8 workbench.

@techTFQ 10 ай бұрын

I would say intermediate .. in MySQL you can solve it using recursive sql .. I’ve shared the solution in my blog

@in-and-around3681 10 ай бұрын

Hi sir i have I sql interview query please help me. Write a query to get all months end date of any year or given year.

@joerokcz 10 ай бұрын

Please upload more

@techTFQ 10 ай бұрын

noted will do

@kk-rk9xg 10 ай бұрын

Will you be launching an SQL bootcamp later this year?

@techTFQ 10 ай бұрын

Yes I’ll launch a recorded sql course next month

@mdammar 10 ай бұрын

Keep price less 😅

@Jami_Sarat_Kumar580 10 ай бұрын

Good explanation , But generate_series not available in sql server management studio 2022. And i have one doubt ISNULL replacing null values and COALESCE its returns 1st non null value of in our table data am i right? Then what is difference main between ISNULL and coalesc? Can you please tell me tfq

@ManpreetSingh-tv3rw 10 ай бұрын

you can use recursive cte to do the same thing, I have mentioned in comments my solution for sql server

@user-rr1mf4tm7z 10 ай бұрын

Sir I have a task releted to banking sector for Sql how can I send you question

@techTFQ 10 ай бұрын

email me

@mmanoj3287 10 ай бұрын

I have one interview Question I can’t answer, can you help me Q : one table have 30 Indexes how to find good and bad one In SqlServer

@powerstock9464 10 ай бұрын

Not sure If I am new to SQL or this was very confusing for me :(

@nachiketpalsodkar4356 10 ай бұрын

Another Simpler WAY!!!! ; WITH cte as ( SELECT e.id,title,groups,levels,payscale,name,totalpost , ROW_NUMBER() OVER(PARTITION BY p.groups ORDER BY p.groups) as RNK FROM job_employees as e CROSS JOIN job_positions as p ) ,cte1 as ( SELECT * FROM cte WHERE RNK

@techTFQ 10 ай бұрын

Nice .. thanks for sharing

@nachiketpalsodkar4356 10 ай бұрын

@@techTFQ Most welcome

@florincopaci6821 10 ай бұрын

Your solution return incorect results.

@nachiketpalsodkar4356 10 ай бұрын

@@florincopaci6821 Kindly check the table names that you are using also i have used SQL server for the solution

@florincopaci6821 10 ай бұрын

@@nachiketpalsodkar4356 You are right. Your solution gave correct Output in Sql Server and Postgresql but in Oracle-NO. Thank you

@Manojkumar__ 10 ай бұрын

where can I learn these kind of new functions like Generate_series?

@techTFQ 10 ай бұрын

google bro, i am not aware of any cheat sheet with this info

@life69kk 10 ай бұрын

Please make plsql course?

@techTFQ 10 ай бұрын

I’m planning for next year

@manoj_m0393 10 ай бұрын

This seems so overwhelming

@durganjaneyulukatiki1781 10 ай бұрын

Hi Thofic i want sql course recording classes

@techTFQ 10 ай бұрын

recorded sql course should release next month

@luckyyyyy790 10 ай бұрын

bro make a video on keys in sql

@techTFQ 10 ай бұрын

you mean constraints?

@luckyyyyy790 10 ай бұрын

yes sir @@techTFQ

@luckyyyyy790 10 ай бұрын

when can i expect a video sir

@techTFQ 10 ай бұрын

Not really planning on it .. I teach it during my sql course

@luckyyyyy790 10 ай бұрын

Where can I get your SQL course

@Alexpudow 5 ай бұрын

with a as ( select position_id, char(null) name, cnts, totalpost from (select distinct position_id, totalpost, totalpost - count(a.id) over(partition by position_id) cnts, count(a.id) over(partition by position_id) cnt from job_employees a join job_positions b on a.position_id = b.id) t where cnt