A great question & a great explained answer. But I find it funny that this was asked in Capgemini, either interviewee claimed to be very good in SQL or interviewer was frustrated from his/her job in Capgemini.
Hi Thoufiq , I have been following your channel from past 1.5years it was helped me lot for sql and absolutely you are doing a great job .we all knew how much effort you are putting to create this series for sql and please try to create the same series for python also.Thank you so much for your hardwork☺️.
I am an Odin student. No placement support nothing. It's been 3 months since I completed the course. Not a single vacancy I got in placement assistance. Anyway you are the best teacher after completing the course still clearing doubts from your videos.
people from iit, even after completing full time course are not getting placed. Doing these online course will not help. There was a time in the past when these quick certifications would work, but right now its not possible
for mysql - with cte as ( select * , SUM(weight_kg) OVER (partition by lift_id order by weight_kg) as running_weight from lift_passengers lp JOIN lifts ON lift_id = id) select lift_id, group_concat(passenger_name) as passengers from cte where running_weight
with cte as (select * , sum(weight_kg) over(partition by lift_id order by weight_kg) runing from lift_passengers) select c.lift_id,group_concat(c.passenger_name) as passenger from cte as c join lifts as l on c.lift_id = l.id and l.capacity_kg >= c.runing group by c.lift_id
Thanks for the Beautiful SQL question. Here is my Solution to the Problem. WITH CTE as ( select p.*,l.*,SUM(weight_kg) OVER (Partition By id ORDER BY weight_kg) as Cumulative_Sum from lift_passengers p LEFT JOIN lift l ON p.lift_id = l.id) SELECT string_agg(passenger_name,',') as Passenger_Name from ( SELECT *,CASE WHEN Cumulative_Sum
Below method is using CTE and this works in Oracle SQL: with new_table as (select lp.*, sum(weight_kg) over (partition by lift_id order by weight_kg asc) as sum_weight from lift_passengers lp) select l.id, LISTAGG(nt.PASSENGERS_NAME, ',') from lift l inner join new_table nt on nt.sum_weight < l.CAPACITY_KG and nt.lift_id =l.id group by l.id
Ms sql: with cte as( select p.* ,l.weight, case when sum(weight_kg) over(partition by p.lift_id order by weight_kg) < l.weight then 1 else 0 end flag from passengers p inner join lift l on l.lift_id=p.lift_id) select lift_id, string_agg(passengername , ',') name from cte where flag=1 group by lift_id order by lift_id;
drop table if exists lift; create table lift( id int, capacity_kg int ); drop table if exists lift_passengers; create table lift_passengers( passenger_name varchar(50), weight_kg int, lift_id int ); insert into lift values (1,300); insert into lift values (2,350); insert into lift_passengers values ('Rahul',85,1); insert into lift_passengers values ('Adarsh',73,1); insert into lift_passengers values ('Riti',95,1); insert into lift_passengers values ('Dheeraj',80,1); insert into lift_passengers values ('Vimal',83,2); insert into lift_passengers values ('Neha',77,2); insert into lift_passengers values ('Priti',73,2); insert into lift_passengers values ('Himanshi',85,2); select * from lift; select * from lift_passengers;
Hi Sir, Thank you for your videos hereby sharing my solution with cte as ( select *,sum(weight_kg) over(partition by lift_id order by weight_kg) as running_weight from lift_passengers P join lifts L on p.lift_id =l.id ) select lift_id ,STRING_AGG(Passenger_name,',') as list_passenger from cte where running_weight
with cte as (select lift_id,passenger_name,weight_kg,sum(weight_kg) over(partition by lift_id order by weight_kg) as cc, capacity_kg from lift_passengers join lifts on lift_passengers.lift_id=lifts.id ), cte2 as(select *,case when cc>capacity_kg then 'n' else 'y' end as 'ff' from cte ) select lift_id,string_agg(passenger_name,', ') as passengers from cte2 where ff'n' group by lift_id
Thanks ! You are back with new challenge , Thank you for the video, everytime I learn something new or different aporoach solving queries, thank you very much.
with cte as (select *, sum(weight_kg) over (partition by lift_id order by weight_kg) as sum_weights from lift_passengers) ,cte2 as ( select c.passenger_name, l.id, case when c.sum_weights - l.capacity_kg
My Solution in MSSQL: with cte as( Select *, sum(weight_kg) over (partition by lp.lift_id order by weight_kg) as wt_sum from lift_passengers lp inner join lifts l on lp.lift_id=l.id) select string_agg(passenger_name,','),lift_id from cte where wt_sum
with cte as ( select lp.passenger_name, lp.weight_kg, sum(weight_kg) over (partition by lift_id order by weight_kg) as cumu_sum,l.capacity_kg,lp.lift_id from lift_passengers lp join lifts l on l.id=lp.lift_id ) select lift_id,string_agg(passenger_name,' , ') from cte where cumu_sum
My solution using SQL Server: with main as ( select lp.lift_id, l.capacity_kg, lp.passenger_name, weight_kg, sum(weight_kg) over(partition by lp.lift_id order by weight_kg range between unbounded preceding and current row ) as Total_Kg from lifts l inner join lift_passengers lp on l.id=lp.lift_id ), Overlift_Capacity_check as ( select * , case when Total_Kg < capacity_kg then 1 else 0 end as Overlift_Flag from main) select lift_id,STRING_AGG(passenger_name,',') as passengers from Overlift_Capacity_check where Overlift_Flag 0 group by lift_id;
with cte as ( select *,sum(weight_kg) over (partition by lift_id order by weight_kg) chk from lift_passengers a left join lifts b on a.lift_id=b.id) select id,STRING_AGG(passenger_name,',') as Outputs from cte where chk
with paa as( select *, sum(weight_kg) over(partition by id order by id, weight_kg) runing_total from lift_passengers join lift on id=lift_id ), stg AS(select * from PAA where capacity_kg>=runing_total) select LIFT_ID, STRING_AGG(passenger_name, ',') from stg GROUP BY LIFT_ID
for the sum aggregate function , inside over() with order by clause gives running total without order by clause gives total sum within the window why it is so???
Hi there, if you haven't found the answer yet, here's an explanation, All window functions have a default frame clause 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' if not specified explicitly. So, when you write a window clause for Sum without an 'order by ' i.e.: Sum(weight_kg) over (partition by lift_id) , the SQL server, by default considers the ORDER BY clause to be ordered by "lift_id" and the frame clause to be 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' therefore, the sum function operates in a window where weight is calculated for lift_id and also orders for lift_id automatically (since you didn't specify it explicitly). NOTE: 'Window/ frame clause, i.e., RANGE BETWEEN UNBOUNDED...' doesn't have any effect here, it only comes into play when order by column has repeated values and if you are in need to tell apart such rows of that column
Hi sir, Could you update if you are working on any sql course like you mentioned in your previous servey? I have been waiting on update since that post
with cte1 as ( select *, sum(weight_kg) over(partition by lift_id order by weight_kg) as running_sum from lift_passengers lp inner join lift l on lp.lift_id = l.id), cte2 as (select *, case when capacity_kg>= running_sum then 1 else 0 end as runn_sum from cte1) select id , STRING_AGG(passenger_name, ',') as total_pass from cte2 group by id
Hi Toufeeq, Here is my Solution : with weights_per_lift as ( SELECT LP.Passenger_name, LP.Weight_kg, LP.Lift_id, L.CAPACITY_KG from LIFT_PASSENGERS LP join LIFT L on L.id=LP.LIFT_ID ) , max_person_per_lift_capacity as ( SELECT passenger_name, weight_kg, lift_id, sum(weight_kg)over(partition by lift_id order by weight_kg) as weights, capacity_kg from weights_per_lift ) SELECT lift_id, GROUP_concat(passenger_name separator ',') as Passengers from max_person_per_lift_capacity where capacity_kg >= weights GROUP by lift_id
with cte as (select *, sum(weight) over(partition by liftid order by weight) as r from lift_pas) select cte.liftid, string_agg(cte.name,',') as pas from cte join lift l on cte.liftid = l.id and cte.r
i wanted to ask one question because i am not getting it that much i want at a beginner level i have just completed begginner sql and pyhton course but some things are getting really complx for me this such question which has taught in this video is it for beginners or is it for advance ?can someone help me with this plz.
for MySql with cte as (select * , sum(weight_kg) over(partition by id order by id,weight_kg) as cummulative_sum, case when capacity_kg>= sum(weight_kg) over(partition by id order by id,weight_kg) then 1 else 0 end as flag from lifts join lift_passengers on id=lift_id order by id,weight_kg) SELECT lift_id, GROUP_CONCAT(passenger_name ORDER BY passenger_name SEPARATOR ', ') AS passenger from cte where flag=1 group by lift_id;
Here is mysolution using MYSQL with cte as (select lp.passenger_name,lp.lift_id,sum(lp.weight_kg) over (partition by lp.lift_id order by lp.weight_kg) as wt,l.capacity_kg from lift_passengers lp left join lifts l on lp.lift_id=l.id) select lift_id,group_concat(passenger_name) as passenger_name from cte where wt
My solution :- 1)On ms sql server with t1 as (select li.id, sum(weight_kg)over(partition by lift_id order by weight_kg) as cum_sum,passenger_name,li.capacity_kg from lift_passengers as lp join lifts as li on lp.lift_id=li.id) select id, STRING_AGG(passenger_name,',') as passengers from t1 where cum_sum
Hi taufiq, I didn't get your solution. I'm little confused. Our task was to find the list of passengers who can be accomodated in lift without exceeding lift capacity, right? So, Shouldn't there be all the possible combinations for each lift 1 & 2, for e.g. for lift 1: { rahul, adarsh, riti},{rahul, adarsh,dheeraj},{adarsh, riti, dheeraj},{ rahul, riti, dheeraj} like that but in increasing order of weight?? Please clarify me. Thanks
Exactly, I resonate you. Just asking this question here (out of curiosity) that, If we have to get all the possible combinations, Is it possible to achieve just by SQL alone? or should we be using any programming language like Python/Java etc? Please can anyone respond from this forum...
Exactly my thought as well when i first read the question. But then why would they give lift id against the passengers. I guess we will need to accommodate Riti (in this case) on a separate row, with lift id 1, which will make more sense (Unlike the sample output).
with cte as( select *,sum(weight_kg) over(partition by id order by weight_kg) as cum_sum,if(capacity_kg>=sum(weight_kg) over(partition by id order by weight_kg),1,0)as flag from lift2 as e join lift_passengers2 as f on e.id=f.lift_id) select lift_id,string_agg(passengers_name,',') from cte where flag=1 group by lift_id; string_aggregate is not working th sql workbench , can we use any alternative other than string_agg() function?
MySql Solution: with cte as ( select passenger_name, weight_kg, lift_id, capacity_kg, sum(weight_kg) over (partition by lift_id rows between unbounded preceding and current row) as cum_sum from lift_passengers as lp inner join lifts as l on l.id = lp.lift_id ) ,cte2 as ( select * from cte where cum_sum < capacity_kg ) select lift_id, group_concat(passenger_name) as passengers from cte2 group by lift_id
with cte as ( select l.*,lp.*, sum(lp.WeightKG) over(partition by l.id order by l.id,lp.WeightKG) as w from LiftPassengers lp left join Lift l on lp.LiftID =l.ID ) select ID, STRING_AGG(PassengerName, ' , ') as passengers from cte where w< CapacityKG group by id
here is my solution with CTE as ( select name, weight , liftid, capacity from lift_passengers a join lifts b on a.liftid = b.id) ,CTE2 as ( select name, weight, liftid, capacity, sum(weight) over(partition by liftid order by weight) cumm_weight from CTE) select liftid, string_agg(name, ',') as passengers from CTE2 where cumm_weight
Everything is fine with your videos other then just 1 major drawback Please whenever you alias something use "AS" Its very hard to understand when you don't use "AS"
I am bit late to post answer but enjoy your problems. i didn't still watch full but here it is my answer: with cte_1 as ( Select * from ( Select Name,ID,capacity,total, flag_sum,case when flag_sum>capacity then 'N' else 'Y' end as flag from( Select Name,ID,capacity,total,sum(total) over (partition by id,capacity order by total) as flag_sum from ( Select Name,ID,capacity,weight as total from Lift_passenger join lift on lift_id=id ) A)B)C where Flag'N') --Select ID,Name from cte_1; Select distinct A.ID,STUFF((Select distinct ', '+B.Name from CTE_1 B where A.id=B.id for XML path (''),TYPE ).value('.', 'NVARCHAR(MAX)'),1,2,'') Passenger from CTE_1 A Happy Learning☺
select lift_id,group_concat(passengername) from (select l.id as lift_id,l.capacity_kg,lp.lift_if,lp.passenger_name as passengername,lp.weight_kg, sum(lp.weight_kg) over(partition by lp.lift_if order by lp.weight_kg asc) as total_weight, case when l.capacity_kg>sum(lp.weight_kg) over(partition by lp.lift_if order by lp.weight_kg asc) then 1 else 0 end as wight_req from lift l join lift_passengers lp on l.id=lp.lift_if) as x where wight_req=1 group by 1;
create table details as (with a as (select * , row_number() over(partition by lift_id order by weight asc) as ranks from passenger) select * , sum(weight) over(partition by lift_id order by ranks) as running_weight from a ); select * from details; with a as ( select passenger_name , lift_id from detailsss where running_weight
@@office4321 I'm not aware of any particular resource, I think it comes with experience. I remember myself too getting amazed at some things which were new to me.
with cte as (select*, count(*) as sums from Trips join Users on client_id = users_id where banned = 'No' group by request_at) select cte.request_at 'Day', round(count(id)/cte.sums, 2) 'Cancellation Rate' from cte where cte.status in ('cancelled_by_driver', 'cancelled_by_client') group by request_at; Can someone please find the error in this code I am getting runtime error saying "FROM keyword not found where expected"
with primary_table as (Select lp.*,capacity_kg from lift_passengers lp left join lifts l on lp.lift_id=l.id), cum_weight_table as (Select passenger_name, lift_id, capacity_kg, sum(weight_kg) over (PARTITION by lift_id order by weight_kg asc rows between unbounded preceding and current row) as cum_weight from primary_table), flag_table as (Select *, case when cum_weight
select id, group_concat(name) from ( select *, sum(wt) over(partition by id order by wt) sop from ( select lp.name, lp.wt, l.id, l.capacity from lift_passengers lp inner join lift l on l.id = lp.lift_id )a)b where sop < capacity group by 1 for mysql
My Solution: with cte as (select l.PASSENGER_NAME, l.WEIGHT_KG, sum(WEIGHT_KG) over(partition by l.lift_id ORDER BY l.PASSENGER_NAME rows between unbounded preceding and current row) as cur_wt_on_lift, l2.id, l2.CAPACITY_KG from lift_passengers l join lifts l2 on l.LIFT_ID = l2.id), cte2 as(select id, PASSENGER_NAME from cte where CAPACITY_KG >= CUR_WT_ON_LIFT) select id, listagg(PASSENGER_NAME, ',') as PASSENGER_NAME from cte2 group by id
My Solution with cte as ( select lp.*, l.capacity_kg , sum(weight_kg) over(partition by lift_id order by weight_kg) as rollsum from lift_passengers lp left join lifts l on lp.lift_id = l.id ) select lift_id , string_agg(passenger_name, ',') from cte where capacity_kg >= rollsum group by lift_id ;
With CTE AS (SELECT B.lift_id,B.passenger_name,B.weight_kg, Sum(B.weight_kg)over(partition by A.id order by B.lift_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RN from lifts A left Join lift_passengers B on A.id = B.lift_id), CTE2 as (Select lift_id, (case when RN < 300 and lift_id = 1 then passenger_name WHEN RN < 350 and lift_id = 2 then passenger_name else NULL END) as RT2 from CTE) SELECT lift_id, String_agg(RT2, ',') as logo from CTE2 group by lift_id;
create table lifts
(
id int
, capacity_kg int
);
insert into lifts values (1, 300);
insert into lifts values (2, 350);
create table lift_passengers
(
passenger_name varchar(50)
, weight_kg int
, lift_id int
);
insert into lift_passengers values ('Rahul', 85, 1);
insert into lift_passengers values ('Adarsh', 73, 1);
insert into lift_passengers values ('Riti', 95, 1);
insert into lift_passengers values ('Dheeraj', 80, 1);
insert into lift_passengers values ('Vimal', 83, 2);
insert into lift_passengers values ('Neha', 77, 2);
insert into lift_passengers values ('Priti', 73, 2);
insert into lift_passengers values ('Himanshi', 85, 2);
Thanks for providing script. This will definitely help most of us
thank you for script
Thoughtful and much needed
Thank you so much :)
You're an angel
A great question & a great explained answer.
But I find it funny that this was asked in Capgemini, either interviewee claimed to be very good in SQL or interviewer was frustrated from his/her job in Capgemini.
Thoufiq you are the real mentor good teacher waiting for more videos and live advance SQL classes.😊
Hi Thoufiq , I have been following your channel from past 1.5years it was helped me lot for sql and absolutely you are doing a great job .we all knew how much effort you are putting to create this series for sql and please try to create the same series for python also.Thank you so much for your hardwork☺️.
Thanks Anusha .. appreciate your kind words.. I’ll think about your suggestions 👍
I am an Odin student. No placement support nothing. It's been 3 months since I completed the course. Not a single vacancy I got in placement assistance. Anyway you are the best teacher after completing the course still clearing doubts from your videos.
This is called real facts 😂😂😂
people from iit, even after completing full time course are not getting placed. Doing these online course will not help. There was a time in the past when these quick certifications would work, but right now its not possible
Which Batch DS3A😄
What they r telling regarding job assistance?
Hi can we connect? I'm also from odinschool
Thanks Toufik,
However You considered only one combination but There could be multiple combination from lift 1 of those having sum of weight
+1
A solution with output as you mentioned would go better with the requirement of the question.
for mysql -
with cte as (
select * ,
SUM(weight_kg) OVER (partition by lift_id order by weight_kg) as running_weight
from lift_passengers lp JOIN
lifts ON lift_id = id)
select lift_id,
group_concat(passenger_name) as passengers from cte
where running_weight
😅
Glad to see you back again sir 😌
Welcome back sir
Thank you :)
with cte as
(select * ,
sum(weight_kg) over(partition by lift_id order by weight_kg) runing
from lift_passengers)
select c.lift_id,group_concat(c.passenger_name) as passenger
from cte as c
join lifts as l
on c.lift_id = l.id and l.capacity_kg >= c.runing
group by c.lift_id
Welcome back Touafiq
Thank you :)
Thanks for the Beautiful SQL question. Here is my Solution to the Problem.
WITH CTE as (
select p.*,l.*,SUM(weight_kg) OVER (Partition By id ORDER BY weight_kg) as Cumulative_Sum from lift_passengers p
LEFT JOIN lift l
ON p.lift_id = l.id)
SELECT string_agg(passenger_name,',') as Passenger_Name from (
SELECT *,CASE WHEN Cumulative_Sum
Below method is using CTE and this works in Oracle SQL:
with new_table as (select lp.*,
sum(weight_kg) over (partition by lift_id order by weight_kg asc) as sum_weight
from lift_passengers lp)
select l.id, LISTAGG(nt.PASSENGERS_NAME, ',')
from lift l
inner join new_table nt on nt.sum_weight < l.CAPACITY_KG and nt.lift_id =l.id
group by l.id
Well explained! Intuitive and to the point
Ms sql:
with cte as(
select p.* ,l.weight, case when sum(weight_kg) over(partition by p.lift_id order by weight_kg) < l.weight then 1 else 0 end flag
from passengers p
inner join
lift l
on l.lift_id=p.lift_id)
select lift_id, string_agg(passengername , ',') name
from cte
where flag=1
group by lift_id
order by lift_id;
drop table if exists lift;
create table lift(
id int,
capacity_kg int
);
drop table if exists lift_passengers;
create table lift_passengers(
passenger_name varchar(50),
weight_kg int,
lift_id int
);
insert into lift values (1,300);
insert into lift values (2,350);
insert into lift_passengers values ('Rahul',85,1);
insert into lift_passengers values ('Adarsh',73,1);
insert into lift_passengers values ('Riti',95,1);
insert into lift_passengers values ('Dheeraj',80,1);
insert into lift_passengers values ('Vimal',83,2);
insert into lift_passengers values ('Neha',77,2);
insert into lift_passengers values ('Priti',73,2);
insert into lift_passengers values ('Himanshi',85,2);
select * from lift;
select * from lift_passengers;
For my fellow Americans Lift = Elevator. 😀
Welcome back Thoufiq!!!
Hi Sir,
Thank you for your videos
hereby sharing my solution
with cte as (
select *,sum(weight_kg) over(partition by lift_id order by weight_kg) as running_weight from lift_passengers P
join lifts L
on p.lift_id =l.id
)
select lift_id
,STRING_AGG(Passenger_name,',') as list_passenger
from cte
where running_weight
Excellent explanation
How to retrieve all sets of lift passengers combinations for two sets of given lifts weight?
It's an extension to the question??
yes, the original question is somewhat stupid.
Could you please do a video where you explain the select statement or maybe the aggregates with group by and having ? Thank you for your work 🙏🏼
I miss your videos. Thank you for this one.
Explanation is awesome 💯
with cte as
(select lift_id,passenger_name,weight_kg,sum(weight_kg) over(partition by lift_id order by weight_kg) as cc, capacity_kg from lift_passengers
join lifts on lift_passengers.lift_id=lifts.id
),
cte2 as(select *,case when cc>capacity_kg then 'n'
else 'y'
end as 'ff'
from cte
)
select lift_id,string_agg(passenger_name,', ') as passengers from cte2 where ff'n' group by lift_id
Thanks ! You are back with new challenge , Thank you for the video, everytime I learn something new or different aporoach solving queries, thank you very much.
RITI as a girl is offended to be 95 KG 😂 , just kidding
well explained sir , want more problems like this
Why do not you teach PL\SQL?
Thanks for the great video 🎉
Capegemini interview very simple I have attended three times cleared three times as well
For which role?
Ouais, on te croit pas gros, sa sent la merde.
Feku chand
Please upload the Create and insert script, so that lot of time will be saved during practice.
good question
with cte as (select *,
sum(weight_kg) over (partition by lift_id order by weight_kg) as sum_weights
from lift_passengers)
,cte2 as (
select c.passenger_name, l.id,
case when c.sum_weights - l.capacity_kg
My Solution in MSSQL:
with cte as(
Select *,
sum(weight_kg) over (partition by lp.lift_id order by weight_kg) as wt_sum
from lift_passengers lp
inner join lifts l on lp.lift_id=l.id)
select string_agg(passenger_name,','),lift_id
from cte
where wt_sum
with cte as (
select lp.passenger_name,
lp.weight_kg,
sum(weight_kg) over (partition by lift_id order by weight_kg) as cumu_sum,l.capacity_kg,lp.lift_id
from lift_passengers lp
join lifts l on l.id=lp.lift_id
)
select lift_id,string_agg(passenger_name,' , ')
from cte
where cumu_sum
Thanks for the video ❤
Hi can you explain about the cursor
My solution using SQL Server:
with main as (
select lp.lift_id,
l.capacity_kg,
lp.passenger_name,
weight_kg,
sum(weight_kg) over(partition by lp.lift_id order by weight_kg
range between unbounded preceding and current row ) as Total_Kg
from lifts l inner join lift_passengers lp on l.id=lp.lift_id
),
Overlift_Capacity_check as
(
select * , case when Total_Kg < capacity_kg then 1 else 0 end as Overlift_Flag
from main)
select lift_id,STRING_AGG(passenger_name,',') as passengers
from Overlift_Capacity_check
where Overlift_Flag 0
group by lift_id;
Hi sir , instead of using string_agg shall we use listag is that possible
I too thinking the same
Thank you bro👌
Thank you sir
just for info.... ORDER BY can't use in CTE statement... correct me if i am wrong
Within Post Gre SQL yes, but not SQL Server.
But your also ordering it within the SUM(weight_kg) window function so you should be good either way.
with cte as (
select *,sum(weight_kg) over (partition by lift_id order by weight_kg) chk from lift_passengers a left join lifts b on a.lift_id=b.id)
select id,STRING_AGG(passenger_name,',') as Outputs from cte where chk
with paa as(
select *, sum(weight_kg) over(partition by id order by id, weight_kg) runing_total from lift_passengers
join lift on id=lift_id ),
stg AS(select * from PAA where capacity_kg>=runing_total)
select LIFT_ID, STRING_AGG(passenger_name, ',') from stg
GROUP BY LIFT_ID
wow great
Is this String_agg function will work on all the environments
Hey Toufiq, if we dont want to use the string_agg function, what other method can we use?
My first thought came to use recurssive
very helpful
for the sum aggregate function ,
inside over()
with order by clause gives running total
without order by clause gives total sum within the window
why it is so???
Hi there, if you haven't found the answer yet, here's an explanation,
All window functions have a default frame clause 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' if not specified explicitly.
So, when you write a window clause for Sum without an 'order by ' i.e.:
Sum(weight_kg) over (partition by lift_id) , the SQL server, by default considers the ORDER BY clause to be ordered by "lift_id" and the frame clause to be 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'
therefore, the sum function operates in a window where weight is calculated for lift_id and also orders for lift_id automatically (since you didn't specify it explicitly).
NOTE: 'Window/ frame clause, i.e., RANGE BETWEEN UNBOUNDED...' doesn't have any effect here, it only comes into play when order by column has repeated values and if you are in need to tell apart such rows of that column
I would suggest you layoff is going in IT so wait atleast 3 month wherever you r
Good job
can we solve this problem without using window function
justice for Riti 😢
😂
For what designation and experience level was this for?
this question is there in the Ankit bansal's 100 sql question course
Hi sir,
Could you update if you are working on any sql course like you mentioned in your previous servey?
I have been waiting on update since that post
what about Lift_ID -- ( 80+85+95 < 300 ). this is also a combination, right? why can't we have this combination.
with cte1 as (
select *, sum(weight_kg) over(partition by lift_id order by weight_kg) as running_sum
from lift_passengers lp
inner join lift l on lp.lift_id = l.id),
cte2 as (select *, case when capacity_kg>= running_sum then 1 else 0 end as runn_sum
from cte1)
select id , STRING_AGG(passenger_name, ',') as total_pass
from cte2
group by id
Hi Toufeeq, Here is my Solution : with weights_per_lift as (
SELECT
LP.Passenger_name,
LP.Weight_kg,
LP.Lift_id,
L.CAPACITY_KG
from
LIFT_PASSENGERS LP
join
LIFT L
on L.id=LP.LIFT_ID
) , max_person_per_lift_capacity as (
SELECT
passenger_name,
weight_kg,
lift_id,
sum(weight_kg)over(partition by lift_id order by weight_kg) as weights,
capacity_kg
from weights_per_lift
)
SELECT
lift_id,
GROUP_concat(passenger_name separator ',') as Passengers
from
max_person_per_lift_capacity
where
capacity_kg >= weights
GROUP
by lift_id
Nice one
Thanks 🔥
with cte as
(select *, sum(weight) over(partition by liftid order by weight) as r
from lift_pas)
select cte.liftid, string_agg(cte.name,',') as pas from cte join lift l on cte.liftid = l.id
and cte.r
I am a beginner but in know thw concepts but i am not able to implement plz help me
i wanted to ask one question because i am not getting it that much i want at a beginner level i have just completed begginner sql and pyhton course but some things are getting really complx for me this such question which has taught in this video is it for beginners or is it for advance ?can someone help me with this plz.
dataset ?
please provide me dataset link / query
for MySql
with cte as
(select * ,
sum(weight_kg) over(partition by id order by id,weight_kg) as cummulative_sum,
case when capacity_kg>= sum(weight_kg) over(partition by id order by id,weight_kg)
then 1
else 0
end as flag
from lifts
join lift_passengers
on id=lift_id
order by id,weight_kg)
SELECT lift_id, GROUP_CONCAT(passenger_name ORDER BY passenger_name SEPARATOR ', ') AS passenger
from cte
where flag=1
group by lift_id;
how would you estimate the level of this task? (beginner, intermediate etc). thanks
Intermediate
How many experience he had on this sql
plz share sql coding competition link , i want to participate ,
Let's begin
Here is mysolution using MYSQL
with cte as (select lp.passenger_name,lp.lift_id,sum(lp.weight_kg) over (partition by lp.lift_id order by lp.weight_kg) as wt,l.capacity_kg
from lift_passengers lp
left join lifts l on lp.lift_id=l.id)
select lift_id,group_concat(passenger_name) as passenger_name from cte where wt
Hi I want to learn your bootcamp SQL can share the details please
My solution :-
1)On ms sql server
with t1 as
(select li.id, sum(weight_kg)over(partition by lift_id order by weight_kg) as cum_sum,passenger_name,li.capacity_kg from lift_passengers as lp
join lifts as li on lp.lift_id=li.id)
select id, STRING_AGG(passenger_name,',') as passengers
from t1 where cum_sum
when i using my phone and iam sleeping ,iam struggled a lot in my area they are create sleeping and they are too much struggle for me and my son.
Hi taufiq, I didn't get your solution. I'm little confused. Our task was to find the list of passengers who can be accomodated in lift without exceeding lift capacity, right? So, Shouldn't there be all the possible combinations for each lift 1 & 2, for e.g. for lift 1: { rahul, adarsh, riti},{rahul, adarsh,dheeraj},{adarsh, riti, dheeraj},{ rahul, riti, dheeraj} like that but in increasing order of weight?? Please clarify me. Thanks
Exactly, I resonate you.
Just asking this question here (out of curiosity) that, If we have to get all the possible combinations, Is it possible to achieve just by SQL alone? or should we be using any programming language like Python/Java etc? Please can anyone respond from this forum...
+1
Exactly my thought as well when i first read the question. But then why would they give lift id against the passengers. I guess we will need to accommodate Riti (in this case) on a separate row, with lift id 1, which will make more sense (Unlike the sample output).
with cte as(
select *,sum(weight_kg) over(partition by id order by weight_kg) as cum_sum,if(capacity_kg>=sum(weight_kg) over(partition by id order by weight_kg),1,0)as flag
from lift2 as e
join lift_passengers2 as f
on e.id=f.lift_id)
select lift_id,string_agg(passengers_name,',') from cte
where flag=1
group by lift_id;
string_aggregate is not working th sql workbench ,
can we use any alternative other than string_agg() function?
in MYSQL group_concat with group by
Good to know you still remember your YT password!
yeah, I am glad too :D
same question in meesho also asked
Can any one help me on which SQL, this problem is solved, because i tried with Oracle Sql not able to solve it.
MySql Solution: with cte as (
select passenger_name, weight_kg, lift_id, capacity_kg, sum(weight_kg) over (partition by lift_id rows between unbounded preceding and current row) as cum_sum
from lift_passengers as lp
inner join lifts as l on l.id = lp.lift_id
)
,cte2 as (
select * from cte
where cum_sum < capacity_kg )
select lift_id, group_concat(passenger_name) as passengers from cte2
group by lift_id
with cte as (
select l.*,lp.*, sum(lp.WeightKG) over(partition by l.id order by l.id,lp.WeightKG) as w from LiftPassengers lp
left join Lift l
on lp.LiftID =l.ID
)
select ID, STRING_AGG(PassengerName, ' , ') as passengers from cte
where w< CapacityKG
group by id
here is my solution
with CTE as (
select name, weight , liftid, capacity from lift_passengers a join lifts b on a.liftid = b.id)
,CTE2 as (
select name, weight, liftid, capacity, sum(weight) over(partition by liftid order by weight) cumm_weight from CTE)
select liftid, string_agg(name, ',') as passengers from CTE2 where cumm_weight
Everything is fine with your videos other then just 1 major drawback
Please whenever you alias something use "AS"
Its very hard to understand when you don't use "AS"
Sir please don't advertise odin they are taking only basic. I completed last month. No deeper knowledge at all.
Justice for riti 😮
I am bit late to post answer but enjoy your problems.
i didn't still watch full but here it is my answer:
with cte_1 as (
Select * from (
Select Name,ID,capacity,total, flag_sum,case when flag_sum>capacity then 'N' else 'Y' end as flag from(
Select Name,ID,capacity,total,sum(total) over (partition by id,capacity order by total) as flag_sum from (
Select Name,ID,capacity,weight as total from Lift_passenger join lift
on lift_id=id ) A)B)C where Flag'N')
--Select ID,Name from cte_1;
Select distinct A.ID,STUFF((Select distinct ', '+B.Name from CTE_1 B where A.id=B.id
for XML path (''),TYPE
).value('.', 'NVARCHAR(MAX)'),1,2,'') Passenger from CTE_1 A
Happy Learning☺
select lift_id,group_concat(passengername) from
(select l.id as lift_id,l.capacity_kg,lp.lift_if,lp.passenger_name as passengername,lp.weight_kg,
sum(lp.weight_kg) over(partition by lp.lift_if order by lp.weight_kg asc) as total_weight,
case when l.capacity_kg>sum(lp.weight_kg) over(partition by lp.lift_if order by lp.weight_kg asc) then 1 else 0 end as wight_req
from lift l join lift_passengers lp on l.id=lp.lift_if) as x where wight_req=1 group by 1;
Pyspark Version of this problem :
ruclips.net/video/0n7aGNVCtRc/видео.htmlsi=hnpqw1o3yiNLUWi2
Riti sidhi se jaegi ab 😅
Arey ye zindaa hain
create table details as (with a as (select * , row_number() over(partition by lift_id order by weight asc) as ranks from passenger)
select * , sum(weight) over(partition by lift_id order by ranks) as running_weight from a );
select * from details;
with a as (
select passenger_name , lift_id from detailsss where running_weight
Bhai Jan Hindi mein kyon Nahin content banate ho Hindi mein
passenger p on l.id = p.lift_id isnt?
May I know why he didn't mentioned their l. p.but still it worked?
It works fine unless there's any ambiguity in the column names. Since they're different, it's okay not to mention the table names.
@@Nnirvana Eye opener, where do I get to know these minor quirks?
@@office4321 I'm not aware of any particular resource, I think it comes with experience. I remember myself too getting amazed at some things which were new to me.
Please share This types interview questions solving problems @techTFQ
with cte as
(select*, count(*) as sums from
Trips join Users
on client_id = users_id
where banned = 'No'
group by request_at)
select cte.request_at 'Day', round(count(id)/cte.sums, 2) 'Cancellation Rate' from cte
where cte.status in ('cancelled_by_driver', 'cancelled_by_client')
group by request_at;
Can someone please find the error in this code I am getting runtime error saying "FROM keyword not found where expected"
with primary_table as
(Select lp.*,capacity_kg from lift_passengers lp
left join lifts l
on lp.lift_id=l.id),
cum_weight_table as
(Select passenger_name,
lift_id,
capacity_kg,
sum(weight_kg) over (PARTITION by lift_id order by weight_kg asc
rows between unbounded preceding and current row) as cum_weight
from primary_table),
flag_table as
(Select *,
case when cum_weight
select id, group_concat(name) from (
select *, sum(wt) over(partition by id order by wt) sop from (
select lp.name, lp.wt, l.id, l.capacity from lift_passengers lp inner join lift l on l.id = lp.lift_id
)a)b where sop < capacity
group by 1
for mysql
My Solution:
with cte as (select l.PASSENGER_NAME, l.WEIGHT_KG, sum(WEIGHT_KG)
over(partition by l.lift_id ORDER BY l.PASSENGER_NAME rows between unbounded preceding and current row)
as cur_wt_on_lift, l2.id, l2.CAPACITY_KG
from lift_passengers l join lifts l2 on l.LIFT_ID = l2.id),
cte2 as(select id, PASSENGER_NAME from cte where CAPACITY_KG >= CUR_WT_ON_LIFT)
select id, listagg(PASSENGER_NAME, ',') as PASSENGER_NAME from cte2 group by id
My Solution
with cte as (
select lp.*, l.capacity_kg
, sum(weight_kg) over(partition by lift_id order by weight_kg) as rollsum
from lift_passengers lp
left join lifts l
on lp.lift_id = l.id )
select lift_id , string_agg(passenger_name, ',')
from cte where capacity_kg >= rollsum
group by lift_id ;
select lp.passenger_name+',', lp.lift_id from lift_passengers lp join lift l on lp.lift_id=l.id where sum(weight_kg)
With CTE AS
(SELECT B.lift_id,B.passenger_name,B.weight_kg,
Sum(B.weight_kg)over(partition by A.id order by B.lift_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RN
from lifts A left Join lift_passengers B on A.id = B.lift_id),
CTE2 as
(Select lift_id,
(case when RN < 300 and lift_id = 1 then passenger_name
WHEN RN < 350 and lift_id = 2 then passenger_name else NULL END) as RT2 from CTE)
SELECT lift_id, String_agg(RT2, ',') as logo from CTE2
group by lift_id;