SQL Interview Question - Last Person To Board The Bus

This post walks you through solving a common LeetCode SQL question: finding the last person to board a bus without exceeding its weight limit. The bus can carry up to 1,000 kg, and you are given a table named Q that contains each person’s details including their order to board, weight, and name.

If you want to follow along visually, here’s a video explaining the problem and query solution step-by-step:

Let’s break down the problem and how to solve it using SQL with window functions and common table expressions (CTEs).

Understanding the Problem Statement

You have a table named Q with these columns:

Column	Description
person_id	Unique ID for each person
person_name	Name of the person
weight	Weight of the person in kilograms
turn	Order in which the person boards the bus (1 is first)

The challenge is that the bus can carry a maximum total weight of 1,000 kg.

The goal is to figure out which person boards last without making the total weight exceed 1,000 kg.

For example, if the boarding weights add up like this for five people:

person_name	weight	turn
Alex	350	1
Brian	300	2
Chris	400	3
Dave	200	4
Eva	150	5

Boarding in order, Alex and Brian fit because 350 + 300 = 650 kg. Adding Chris would make 1,050 kg, which is over the limit, so Chris cannot board. The last person who fits without exceeding the 1,000 kg is Brian.

Now, let’s see how to translate this problem into an efficient SQL query.

Ordering Data by Boarding Turn

The first step is to process people in the order they board the bus, which means sorting by the turn column.

Ordering is essential because the cumulative weight depends on these turns. You can't just add random weights—sum must be calculated sequentially.

In SQL, this looks like:

SELECT person_id, person_name, weight, turn
FROM Q
ORDER BY turn;

This simple ordering keeps the list aligned to who boards first, second, and so on.

Without this order, the cumulative sum or total weight calculation would be incorrect.

Calculating Cumulative Weight Using Window Functions

We want to find the running total weight as each person boards.

A cumulative sum (also called running total) adds the current person’s weight to all previous weights in boarding order.

In SQL Server and many other databases, window functions handle this efficiently:

SELECT person_id, person_name, weight, turn,
       SUM(weight) OVER (ORDER BY turn) AS total_weight
FROM Q
ORDER BY turn;

Explanation:

• SUM(weight) sums the weight values.
• OVER (ORDER BY turn) directs that the sum runs over rows in increasing order of turn.
• No partitioning is needed here because all rows contribute to the same total sequence.

The result will look like this:

person_name	weight	turn	total_weight
Alex	350	1	350
Brian	300	2	650
Chris	400	3	1050
Dave	200	4	1250
Eva	150	5	1400

This shows how the accumulated weight increases as each person boards.

Benefits of window functions here:

• Computes running totals without needing complex joins or subqueries.
• Keeps row context intact (you still see each person’s details).
• Easy syntax and good performance.

Identifying Persons Who Fit Within the Weight Limit Using CTE

Next, we want to filter out people who would cause the total weight to exceed the bus limit.

Using a Common Table Expression (CTE) makes this clearer and easier to work with.

A CTE lets you wrap the cumulative sum query, then apply filters on that result.

Example:

WITH CumulativeWeights AS (
    SELECT person_id, person_name, weight, turn,
           SUM(weight) OVER (ORDER BY turn) AS total_weight
    FROM Q
)
SELECT person_name
FROM CumulativeWeights
WHERE total_weight <= 1000;

Here’s what this does:

• The CTE named CumulativeWeights generates a table with each person's cumulative weight.
• The outer query fetches names where cumulative weight does not exceed 1,000 kg.

For the sample data, this returns:

person_name
Alex
Brian

Because Chris’s cumulative weight (1050) is over the limit, he’s excluded.

Using a CTE helps separate logic:

• First create cumulative sums.
• Then filter based on the weight condition.

This organization makes complex queries easier to read, debug, and maintain.

Retrieving the Last Person to Board

Finally, we want to pick only the last person who can board the bus without breaking the limit.

From the filtered list where total_weight <= 1000, retrieve the one with the highest boarding order (turn).

The solution uses the SQL TOP 1 clause with descending order on turn:

WITH CumulativeWeights AS (
    SELECT person_id, person_name, weight, turn,
           SUM(weight) OVER (ORDER BY turn) AS total_weight
    FROM Q
)
SELECT TOP 1 person_name
FROM CumulativeWeights
WHERE total_weight <= 1000
ORDER BY turn DESC;

Breaking down the key parts:

• TOP 1 limits the result to one row.
• ORDER BY turn DESC sorts by boarding order from last to first.
• Combining these gives the last person able to board under the limit.

In the example, Brian is the last person (turn 2) below 1,000 kg total weight.

If you’re using a database without TOP, equivalents are:

• LIMIT 1 (MySQL, PostgreSQL) with ORDER BY turn DESC
• FETCH FIRST 1 ROW ONLY (ANSI SQL standard)

Summary of the final query’s clauses:

• WITH to define the cumulative weights table
• WHERE total_weight <= 1000 to filter passengers that fit
• SELECT TOP 1 person_name to get the last boarder’s name
• ORDER BY turn DESC to pick the boarder with the highest turn within limit

This elegant approach solves the problem in a readable and performant way.

Additional Resources and Learning Opportunities

For those interested in a hands-on walkthrough, the original video titled SQL Interview Question - Last Person To Board The Bus is available on the Learn at Knowstar YouTube channel.

If you want to test your skills or prepare for interviews, consider booking a personalized 1-on-1 SQL mock interview session. It’s a good chance to get tailored feedback.

Here are additional helpful resources to grow your SQL knowledge:

• Explore the Learn at Knowstar blog for more SQL interview questions and data analytics tips.
• Follow their Instagram and LinkedIn pages for updates.
• Check out certifications like the Google Data Analytics Professional Certificate.

Essential Tech Gear for Developers

To stay productive while working on SQL and coding, consider some tech tools frequently recommended by the Learn at Knowstar channel:

• Keychron K8 Wireless Mechanical Keyboard for comfortable typing.
• ASUS ZenScreen Portable Monitor to increase screen space.
• Logitech MX Master 3S Wireless Mouse for precision control.
• Flexispot Electric Standing Desk to reduce sitting time.
• Sony WH-1000XM5 Noise-Cancelling Headphones to focus in noisy environments.

Explore the full curated list with product details and budget options on the Learn at Knowstar tech gear page.

---

This problem shows how useful window functions and CTEs can be when solving real-world sequence-based calculations. Understanding these tools will help you tackle many SQL interview questions with confidence.

Thanks for reading! If you want more SQL interview tips and tutorials, subscribe to the Learn at Knowstar YouTube channel.

Happy querying!