Add Missing Values - SQL Interview Query 8 | SQL Problem Level "MEDIUM"

[@dakshbhatnagar]

Upon encountering the question, I immediately considered applying the second solution, although I wasn't entirely sure how. However, through the course of this video, I gained an understanding of recursive queries in SQL. The video was excellent; I thoroughly enjoyed it.

[@NaveenKumar-qf8nn]

Using First_value function: with cte as (select *, count(job_role) over(order by row_id) as cnt from job_skills) select row_id, first_value(job_role) over(partition by cnt order by row_id) as job_role, skills from cte

[@subodhthore6454]

Good solution to find the group using count, Get to learn new things. I applied lag, sum window function to do that.

[@subodhthore6454]

with cte as ( select *, lag(job_role,1,0) over(order by row_id) as prevRole from job_skills) , cte1 as ( select *, case when job_role is not null and prevRole is null then 1 else 0 end as flag from cte) , cte2 as ( select row_id, job_role,skills, sum(flag) over(order by row_id) as groups from cte1) select row_id, first_value(job_role) over(partition by groups order by row_id) as NewJobRole, skills from cte2;

[@Praveen_Kumar_R_CBE]

really awesome, I always thought recursives are complex.. but you have put it in a more logical and simpler way to understand

[@likinponnanna8990]

The concept of recursive cte was very well explained...!

[@Kirankumar-ml1ro]

select row_id,coalesce(job_role,'Data Engineer'),skills from job_skills where row_id>=1 and row_id=6 and row_id=9 and row_id

[@pragatiaggarwal8103]

Nice solution

[@Hsalz]

Cool! I'm glad I was able to remember the logic of this problem from the last one haha. Neat trick. Thanks for all your hard work!

[@balling220]

Excel does these with super ease SQL needs to simplify stuff

[@dakshbhatnagar]

Let’s say there are 2 MILLION records, excel will not be able to handle these records and if it can, it will be painstakingly slow

[@jollymeelubari9045]

select row_id, CASE WHEN job_role is null THEN 'Data Engineer' ELSE job_role END AS job_role , skills from job_skills WHERE row_id = 6 AND row_id = 9; I solved it using this querry. Thanks for all you do.

[@pragatiaggarwal8103]

impressed

[@nangaveramari7563]

with c as (select *,sum(case when job_role is not null then 1 else 0 end) over(order by row_id) as rn from job_skills), b as (select *,first_value(job_role) over (partition by rn order by row_id) as fv from c) select row_id,fv as job_role, skills from b

[@parmoddhiman678]

with cte as ( SELECT * , sum(case when job_role is not null then 1 else 0 end) over (order by ROW_ID ) as segment FROM jobskills) select row_id , first_value(job_role) over(partition by segment) as job_role, skills from cte

[@pushpalathamunikannu4942]

Thank you for your hardwork. It was easy to understand.

[@shivinmehta7368]

select a.row_id, coalesce(b.job_role,a.job_role) as job_role,a.skills from job_skills a left join (select row_id,job_role,lead(row_id,1,(select max(row_id) from job_skills)) over(order by row_id) as lt from job_skills where job_role is not null) b on a.row_id>b.row_id and a.row_id

[@fury00713]

mysql solution : with cte as ( select *, sum(case when job_role is not null then 1 else 0 end) over(order by row_id) as rolling_sum from job_skills) select row_id, max(job_role) over(partition by rolling_sum) as job_role, skills from cte ;

[@anirbanbiswas7624]

1 Word to say...EXCELLENT @TECHTFQ

[@bassambenidir1729]

This query works as well: SELECT row_id ,case when flag = 1 then 'Data Engineer' when flag = 2 then 'Web Developer' when flag = 3 then 'Data Scientist' END as job_role , skills from (SELECT *, Sum(CASE WHEN job_role IS NULL THEN 0 ELSE 1 END) OVER( ORDER BY row_id) AS flag FROM job_skills ) x;

[@dhanush98b11]

Thank you for doing the such informative sql video's , learning new concepts everyday❤

[@vinuvicky1560]

with cte as( select * , lead(row_id,1 ,30)over(order by row_id) as next_r from job_skills where job_role is not null) select j.row_id, c.job_role, j.skills from cte c join job_skills j on j.row_id>=c.row_id and j.row_id

[@audreaathena4715]

Thank you for posting these challenges. I'm finding out just how much I still have yet to learn about SQL. Incidentally, I tried using your recursion solution and had to omit the keyword RECURSIVE to get it to work. I guess the recursion is implied by using UNION ALL within a CTE, so I've been told. (I'm using SSMS.) -- solution using a window function with cte as ( select * , sum(case when job_role is null then 0 else 1 end) over(order by row_id) as role from #job_skills ) SELECT row_id, FIRST_VALUE(job_role) OVER (PARTITION BY role ORDER BY row_id) AS job_role, skills FROM cte ORDER BY row_id -- solution using a correlated subquery SELECT js.row_id, ( SELECT TOP 1 job_role FROM #job_skills WHERE row_id

[@growtogether1341]

with cte as ( select *, count(job_role) over (order by row_id) as partof from job_skills ) select row_id, case when job_role is null then first_value(job_role) over(partition by partof order by row_id) else job_role end as job_role, skills from cte

[@janakmali8243]

we can also solve using lead function and join method: ;with cte as ( select *, isnull(lead(row_id,1) Over (order by row_id asc),99) as rn from job_skills j where job_role is not null ) select j.row_id, c.job_role, j.skills from cte c Inner Join job_skills j on c.row_id j.row_id

[@sourabroy7787]

wonderful explanation of 2nd solution 👍

[@sahilummat8555]

;with cte as ( select *, lead(row_id,1,99999)over(order by row_id )-1 as next_id from job_skills where job_role is not null) select js.row_id,c.job_role,js.skills from cte c inner join job_skills js on js.row_id between c.row_id and c.next_id

[@khadijasultana8964]

easy to understand👍🏻 good solution

[@TechInMeta]

select row_id, Case When rnkNum=1 then 'Data Engineer' When rnkNum=2 then 'Web Developer' Else 'Data Scientist' End as JobRolDetails,skills from ( select *,count(job_role) over(order by row_id) as rnkNum from job_data ) as OrgTable; This My solution is very simple

[@gowrikm-n5u]

with cte as ( select * , sum(case when job_role is null then 0 else 1 end) over (order by row_id) as r from job_skills ) select c2.job_role,c.skills from cte c join cte c2 on c.r = c2.r where c2.job_role is not null

[@moviebuff3030]

select row_id, case when row_id = 6 and row_id < 9 then 'Web Developer' when row_id >= 9 and row_id

[@MohitYadav-hz8tb]

it is not dynamic

[@iswillia123]

I did this in Oracle: with cte as ( select * from job_skills) select row_id, nvl(job_role_n, job_role ) job_role, skills from ( select row_id, job_role, skills, last_value(job_role ignore nulls) over (order by row_id ) job_role_n from cte) order by row_id; with cte as ( select * from job_skills), jr as (select row_id, job_role, lead(row_id ) over (order by row_id) next_row_id from cte where job_role is not null) select cte.row_id, cte.job_role, cte.skills, jr.job_role casc_job_role from cte left join jr on cte.row_id >= jr.row_id and cte.row_id < nvl(jr.next_row_id,cte.row_id +1) order by cte.row_id;

[@sakthibagavthi2864]

HI bro i get this results but i dont know how update the values can you write a query to an update to that table

[@iswillia123]

@@sakthibagavthi2864 You have to do: Insert into xxxx With cute as ( ) Select * from cte;

[@SakthiBagav96-cd8ji]

@@iswillia123 Thanks bro sure i will try this

[@yasink18]

First time i came into your video... I really liked it❤

[@anushakrishnaa]

with cte as( select *,COUNT(job_role) over(order by row_id) c from job_skills) select *,FIRST_VALUE(job_role) over(partition by c order by row_id)j from cte

[@ehsanshakeri620]

thank you, that was a interesting problem

[@prajaktarajendra7335]

Plz sir create vdo on Execution plan and dynamic stored procedure

[@splendidabhi]

Completed #day8

[@MdZeeshan-m9u]

Thank You for your hardwork .

[@bassambenidir1729]

I've used this query and it seems to be working: SELECT row_id, COALESCE(job_role, ( SELECT job_role FROM job_skills WHERE row_id < js.row_id AND job_role IS NOT NULL ORDER BY row_id DESC LIMIT 1 )) AS job_role, skills FROM job_skills js;

[@amzahaan7232]

select row_id, case when row_id

[@hariikrishnan]

bro u are hardcoding the values, this wont work if you have a bigger dataset

[@deepakramineni1761]

Thank you brother

[@VijayKumarGode-t1c]

select row_id, nvl(job_role,lag(job_role) ignore nulls over(order by row_id)) as job_role , skills from job_skills; select row_id, last_value(job_role) ignore nulls over(order by row_id),skills from job_skills;

[@deepakbehara198]

with cte as( select row_id from job_skills where job_role is not null ), cte1 as( select row_id,coalesce(lead(row_id) over(),(select row_id from job_skills order by 1 desc limit 1)+1) as nxt from cte ), cte2 as( select row_id,nxt,case when nxt-(select row_id from cte1 limit 1) < 0 then row_id else nxt-(select row_id from cte1 limit 1) end as final_g_id from cte1 ), cte3 as( select cte2.row_id,job_role,final_g_id from job_skills j right join cte2 on j.row_id = cte2.row_id ) select row_id,(select job_role from cte3 where job_skills.row_id >= cte3.row_id and job_skills.row_id

[@rajm2950]

Hi I new to sql devlepment if you get time can you explaine the flag logic in details again i strugling littile bit thank you in advance 😀

[@MdZeeshan-m9u]

watch previous day challenge where he has explained in detail

[@saiswaroop3570]

select row_id,nvl(job_role,first_value(job_role)over(partition by roll_sum order by row_id)) as job_role,skills from ( select j.*, sum(case when job_role is not null then 1 else 0 end)over(order by row_id) as roll_sum from job_skills j )

[@rihanalli3754]

select row_id,case when flag=1 then 'data engineer' when flag=2 then 'web developer' else 'data scientist' end as job_roleupdated,skills from( select *,sum( case when job_role is null then 0 else 1 end) over(order by row_id) as flag from job_skills)a order by row_id

[@PriyaKaushal-v5r]

why did you not use partition by in over clause?

[@Satish_____Sharma]

solution using MYSQL with cte as (SELECT row_id, job_role, skills,count(job_role) over (order by row_id) as cnt FROM job_skills) select row_id, first_value(job_role) over (partition by cnt order by row_id) as newjob, skills from cte

[@tusharbhandurge9310]

you can solve by this also---- Oracle SQL select nvl(job_role,lag(job_role)ignore nulls over(order by null))as job_role,skills from job_data;

[@Alexpudow]

select row_id, coalesce(job_role,max(job_role) over(partition by p)) job_role, skills from (select row_id, job_role, skills, count(case when job_role is not null then row_id end) over(order by row_id) p from job_skills) t

[@poojagonla4340]

with job_flag as ( select row_id, case when job_role is null then 0 else 1 end job_role_flag,job_role , skills from job_skills) , partition_data as (Select row_id, sum(job_role_flag) over (partition by '' order by row_id ) partition_flag, job_role, skills from job_flag) Select row_id,min(job_role) over(partition by partition_flag order by row_id) job_role, skills from partition_data;

[@brownwolf05]

----mysql solution with cte_flag as( select *, sum(case when job_role is not null then 1 else 0 end) over(order by row_id) as flag from job_skills ) select row_id,FIRST_VALUE(job_role) over(PARTITION BY flag order by row_id) as job_role, skills from cte_flag;

[@nadimatlanagesh1951]

My solution : WITH cte AS ( SELECT *,count(job_role) OVER (ORDER BY row_id) AS cnt_job_role FROM job_skills) SELECT row_id,first_value(job_role) OVER (PARTITION BY cnt_job_role ORDER BY row_id) AS job_role,skills FROM cte;

[@hariikrishnan]

Insert Queries: create table if not exists job_data ( row_id int not null, job_role text, skills text ) insert into job_data values (1, 'Data Engineer', 'SQL'), (2, null, 'Python'), (3, null, 'AWS'), (4, null, 'Snoflake'), (5, null, 'Apache Spark'), (6, 'Web Developer', 'Java'), (7, null, 'HTML'), (8, null, 'CSS'), (9, 'Data Scientist', 'Python'), (10, null, 'Machine Learning'), (11, null, 'Deep Learning'), (12, null, 'Tableau')

[@NabeelKhan-um1zk]

select row_id , case when row_id in (1,2,3,4,5) then "Data Engineer" when row_id in(6,7,8) then "Web_Developer" else "Data Scientist" end as job_role , skills from job_skills;

[@pragatiaggarwal8103]

impressed

[@darshakkumarranpariya867]

Are these kinds of questions being asked in data analyst interviews?

[@florincopaci6821]

Hello Recusrion to solve this? no way. this can be solved using count(job_role)over(order by row_id) as flag and then first_value but the simplest way to solve this and short is in this way: select row_id, min(job_role) over(order by row_id rows between 4 preceding and current row)as job_role, skills from job_skills; Hope it helps

[@satyajitbiswal6162]

4 preceding concept will not work in all scenarios

[@florincopaci6821]

@@satyajitbiswal6162 în this scenario will work.do you know why? Do you know why is 4 preceding and not 1 2 3 or n.for others scenario you need to ajust that preceding.

[@Pascald-jh8pw]

it s like a fill down for Mysql, if you need a fill up, juste change >= and asc: select t1.row_id, (select job_role from job_skills as t2 where t2.row_id

[@rohitsethi5696]

hi i have query you have given row_id+1 but how it work in 6 colum where row_id+1 would be 7

[@ayushmi77al]

SELECT row_id, FIRST_VALUE(job_role) OVER (PARTITION BY role_segment) AS job_role, skills FROM (SELECT *, SUM(CASE WHEN job_role IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY row_id) AS role_segment FROM job_skills js);

[@AkashRawat-br2ew]

We can do this to get the updated_job_role SELECT row_id,CASE WHEN job_role IS NULL THEN LAG(job_role IGNORE NULLS) OVER (ORDER BY row_id) ELSE job_role END AS updated_job_role FROM job_skills;

[@abhishekgarg9029]

SOLUTION W/O "First_Value" WINDOW FUNC OR RECURSION: with cte as ( select *, sum(case when job_role is not null then 1 else 0 end) over (order by row_id) as role_flag from job_skills) , cte2 as ( select role_flag,max(job_role) as job_role from cte group by role_flag) select c1.row_id,c2.job_role,c1.skills from cte c1 inner join cte2 c2 on c1.role_flag = c2.role_flag

[@haleylearn]

-- SOLUTION 1: WITH get_grp AS ( SELECT * , SUM(CASE WHEN job_role IS NOT NULL THEN 1 ELSE 0 END) OVER(ORDER BY row_id) AS grp FROM job_skills ) SELECT * , MAX(job_role) OVER(PARTITION BY grp) AS result FROM get_grp;

[@PriyaKaushal-v5r]

I have a very confusing question In the first solution, why did you not put partition by in sum(case when) statement along with order by. I am confused when to not user partition by

[@happyclips100]

here we dont need to partition the data. just only sum to get the different value..

[@nikilnikil3675]

WITH cte AS( select *, CASE WHEN job_role IS NOT NULL THEN 1 ELSE 0 END AS job_rolee, SUM(CASE WHEN job_role IS NOT NULL THEN 1 ELSE 0 END) OVER(ORDER BY row_id) from job_skills) SELECT row_id, CASE WHEN job_role is not null then job_role else first_value(job_role) over(partition by sum order by row_id ) end as rolee, skills FROM CTE

[@saitejamanchi1389]

Another approach: with temp as( select row_id, job_role from job_skills where job_role is not null ) , min_max as( select job_role, row_id as min_id, lead(row_id) over (order by row_id) as max_id from temp ) select j.row_id, m.job_role, j.skills from job_skills j join min_max m on j.row_id >= m.min_id and (j.row_id

[@DEwithDhairy]

PySpark Version of this problem : kzbin.info/www/bejne/h3TIiYaMp86psKc

[@MRV9690]

Sir agar hum ise reverse karna chaye to kaise hoga?

[@karangupta_DE]

with cte as ( select job_role, skills, row_number()over(order by (select null)) as rn from job_skills ), cte1 as ( select *, lead(rn)over(order by rn) as next_rn from cte where true and job_role is not null ) select coalesce(c.job_role, c1.job_role) as job_role, c.skills from cte c join cte1 c1 on c.rn >= c1.rn and c.rn < case when c1.next_rn is null then 99999 else c1.next_rn end;

[@vaibhavmehrotra580]

Why this solution is not working :- SELECT row_id, CASE WHEN job_role IS NULL THEN LAG(job_role,1) OVER(ORDER BY row_id) ELSE job_role END as job_role, skills FROM job_skills;

[@CebuProvince]

is the 2nd Solution in the video the same as you offer in the download file😮 - Video_Q8_SOLUTION.txt - the 2nd Solution? -- Solution 2 - WITHOUT Using Window function with recursive cte as (select row_id, job_role, skills from job_skills where row_id=1 union all select e.row_id, case when e.job_role is null then cte.job_role else e.job_role end as job_role , e.skills from job_skills e join cte on e.row_id = cte.row_id + 1 ) select * from cte; - my syntax checker tells me: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'cte'. For me i can'nt find a error Thank you for all that work and explanations, great job. 👍

[@sourabhkumar6811]

with cte1 as (select js.*, row_number()over(order by null)as rn from job_skills js), cte2 as (select cte1.*, lead(cte1.rn,1,100)over(order by row_id)-1 as next_rn from cte1 where job_role is not null) select cte1.row_id,cte2.job_role,cte1.skills from cte2 left join cte1 on cte1.row_id>=cte2.rn and cte1.row_id

[@sanilkumarbarik9151]

with cte1 as ( select *, row_number() over(order by (select null)) as rn from job_skills), cte2 as ( select *, lead(rn, 1, 9999) over(order by rn) as next_rn from cte1 where job_role is not null) select cte1.row_id, cte2.job_role, cte1.skills from cte1 join cte2 on cte1.rn >= cte2.rn and cte1.rn < cte2.next_rn - 1