I came across this problem recently and have been unable to solve it using SQL. The problem appears to be an extension to the original LeetCode problem here

Here is the premise: There is a queue of people waiting to board an elevator. However, the elevator has a maximum weight limit of 1000 kilograms, so people may have to board the elevator in separate trips.

The sample table below shows information about people who are waiting to board. The column turn determines the order in which each person will board. I added the column weight_cumulative for ease of understanding, with the query SUM(weight) OVER (ORDER BY turn) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

I need to return the list of all people who are the last to board the elevator in their trips.

i.e. for the above example, the output would be

(In contrast, the original LeetCode problem only asks for the last person to enter the first and only trip. This is as simple finding the last person ordered by turn where weight_cumulative <= 1000)

The problem would be solved if I can create the column weight_cumulative_tripwise as below

But creating weight_cumulative_tripwise requires some column (like trip_number) that I can use as a partition to run my SUM() window function on top of, but I cannot figure out how to create trip_number without having weight_cumulative_tripwise first.

I am not an expert at recursive CTEs, but this looks a problem that can be solved recursively in a general programming language. Is there a way to do this problem without using recursive CTEs? Ideally using functions that are available in all popular flavors of SQL.