List of all the Last People to Enter the Elevator

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.

The naïve CTE version:

WITH data AS (
    SELECT  *
    FROM
    (
        VALUES  (N'Alice', 250, 1, 250)
        ,   (N'Bob', 170, 2, 420)
        ,   (N'Alex', 350, 3, 770)
        ,   (N'John', 400, 4, 1170)
        ,   (N'Winston', 500, 5, 1670)
        ,   (N'Marie', 200, 6, 1870)
    ) t (name,weight,turn,weight_cumulative)

)
, cte_elevator AS (
    SELECT  name, weight, turn, weight AS total, 1 AS trip
    FROM    data d
    WHERE   turn = 1
    UNION ALL
    SELECT  d.name, d.weight, d.turn
    ,   CASE WHEN e.total + d.weight > 1000 THEN d.weight ELSE e.total + d.weight END
    ,   CASE WHEN e.total + d.weight > 1000 THEN trip + 1 ELSE trip END
    FROM    cte_elevator e
    INNER JOIN data d
        ON  d.turn = e.turn + 1
    )
SELECT  *
FROM    (
    SELECT  *
    ,   ROW_NUMBER() OVER(PARTITION BY trip ORDER BY turn DESC) AS sort
    FROM    cte_elevator
    ) x
WHERE   x.sort = 1

One creates a recursive cte that starts from turn = 1 and for every next person, one compares previous total weight + current person's weight to 1000, and increments the "trip" counter accordingly.

To get the last person per trip, you can use the standard ROW_NUMBER function.

Outputs: