Find Passengers That Can Fit in Each Lift | SQL Interview Question

In this video, we solve an interesting SQL problem that tests your ability to work with aggregations, joins, and string functions.

🧩 Problem Statement:
The relationship between the LIFT and LIFT_PASSENGERS tables is such that multiple passengers can attempt to enter the same lift — but the total weight of the passengers in a lift cannot exceed the lift’s capacity.

👉 Goal:
Write an SQL query that produces a comma-separated list of passengers who can be accommodated in each lift without exceeding its capacity.
The passengers should be listed in increasing order of their weight.

Table Create and Insert statements:

Create Table Lift_Passengers(
Passenger_Name Varchar(20),
Weight_Kg int,
Lift_Id int)

Insert into Lift_Passengers Values('Mark',85,1)
Insert into Lift_Passengers Values('Antony',73,1)
Insert into Lift_Passengers Values('David',95,1)
Insert into Lift_Passengers Values('Mary',80,1)
Insert into Lift_Passengers Values('John',83,2)
Insert into Lift_Passengers Values('Robert',77,2)
Insert into Lift_Passengers Values('Maria',73,2)
Insert into Lift_Passengers Values('Susan',85,2)

Create Table Lift( Id int,
Capacity_Kg Bigint)

Insert into Lift Values(1,300)
Insert into Lift Values(2,350)

📘 What You’ll Learn:

How to use CTEs (Common Table Expressions) for step-by-step logic
How to accumulate sums and filter dynamically
How to generate a comma-separated list in SQL Server
A real-world-style SQL interview problem



🔗Join SQL Junction Community

📢 Telegram: [https://t.me/sql_junction](https://t.me/sql_unction)
💼 LinkedIn: [  / adityabansal095  ]



💡 Don’t Forget To

👍 Like the video
📝 Comment your approach
🔔 Subscribe for more SQL Challenges every week!

#SQLInterview #SQLServer #SQLChallenge #DataEngineering #SQLTutorial #AdityaBansal #SQLJunction #DataScienceInterview #SQLQuestion #TechInterview #AdvancedSQL #interviewprep