Add Missing Values - SQL Interview Query 8 | SQL Problem Level "MEDIUM"
Вставка
- Опубліковано 11 лип 2024
- 30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the EIGHTH video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.
Let's follow the below routine to make the best use of it:
1. Watch the UA-cam video (first half) to understand the problem statement.
2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.
3. Share your solution on Discord and discuss different solutions and issues on my Discord server.
4. Watch the second half of my UA-cam video to find my solution to the problem.
5. Share it with your contacts and spread the knowledge.
DOWNLOAD the Dataset from below:
Discord server: / discord
Blog website: techtfq.com/blog/30daysqlquer...
Timeline:
00:00 Intro
00:10 Understanding Problem Statement
02:34 Solution no 1
09:10 Solution no 2
Thanks for participating in this challenge!
Good luck and Happy Learning!
easy to understand👍🏻 good solution
wonderful explanation of 2nd solution 👍
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!
Thank You for your hardwork .
really awesome, I always thought recursives are complex.. but you have put it in a more logical and simpler way to understand
Thank you for doing the such informative sql video's
, learning new concepts everyday❤
thank you, that was a interesting problem
Thank you for your hardwork. It was easy to understand.
First time i came into your video...
I really liked it❤
Thank you brother
select row_id,
case
when row_id = 6 and row_id < 9 then 'Web Developer'
when row_id >= 9 and row_id
it is not dynamic
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
1 Word to say...EXCELLENT @TECHTFQ
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
Nice solution
Excel does these with super ease
SQL needs to simplify stuff
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
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
Good solution to find the group using count, Get to learn new things. I applied lag, sum window function to do that.
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;
Completed #day8
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
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.
impressed
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
Plz sir create vdo on Execution plan and dynamic stored procedure
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.
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;
hi i have query you have given row_id+1 but how it work in 6 colum where row_id+1 would be 7
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
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
;
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;
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
Are these kinds of questions being asked in data analyst interviews?
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
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
select row_id,
case when row_id
bro u are hardcoding the values, this wont work if you have a bigger dataset
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
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 😀
watch previous day challenge where he has explained in detail
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
)
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;
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
@@sakthibagavthi2864 You have to do:
Insert into xxxx
With cute as (
)
Select * from cte;
@@iswillia123 Thanks bro sure i will try this
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;
Sir agar hum ise reverse karna chaye to kaise hoga?
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
why did you not use partition by in over clause?
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;
----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;
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;
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
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
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
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
here we dont need to partition the data. just only sum to get the different value..
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
4 preceding concept will not work in all scenarios
@@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.
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;
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;
impressed
PySpark Version of this problem :
ua-cam.com/video/QCeXSVrivzE/v-deo.html
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')
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;
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);
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
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
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;
-- 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;
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;
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
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. 👍
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