Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/2153.html
2153. The Number of Passengers in Each Bus II
Description
Table: Buses
+++  Column Name  Type  +++  bus_id  int   arrival_time  int   capacity  int  +++ bus_id is the primary key column for this table. Each row of this table contains information about the arrival time of a bus at the LeetCode station and its capacity (the number of empty seats it has). No two buses will arrive at the same time and all bus capacities will be positive integers.
Table: Passengers
+++  Column Name  Type  +++  passenger_id  int   arrival_time  int  +++ passenger_id is the primary key column for this table. Each row of this table contains information about the arrival time of a passenger at the LeetCode station.
Buses and passengers arrive at the LeetCode station. If a bus arrives at the station at a time t_{bus}
and a passenger arrived at a time t_{passenger}
where t_{passenger} <= t_{bus}
and the passenger did not catch any bus, the passenger will use that bus. In addition, each bus has a capacity. If at the moment the bus arrives at the station there are more passengers waiting than its capacity capacity
, only capacity
passengers will use the bus.
Write an SQL query to report the number of users that used each bus.
Return the result table ordered by bus_id
in ascending order.
The query result format is in the following example.
Example 1:
Input: Buses table: ++++  bus_id  arrival_time  capacity  ++++  1  2  1   2  4  10   3  7  2  ++++ Passengers table: +++  passenger_id  arrival_time  +++  11  1   12  1   13  5   14  6   15  7  +++ Output: +++  bus_id  passengers_cnt  +++  1  1   2  1   3  2  +++ Explanation:  Passenger 11 arrives at time 1.  Passenger 12 arrives at time 1.  Bus 1 arrives at time 2 and collects passenger 11 as it has one empty seat.  Bus 2 arrives at time 4 and collects passenger 12 as it has ten empty seats.  Passenger 12 arrives at time 5.  Passenger 13 arrives at time 6.  Passenger 14 arrives at time 7.  Bus 3 arrives at time 7 and collects passengers 12 and 13 as it has two empty seats.
Solutions

# Write your MySQL query statement below WITH T AS ( SELECT *, sum(cnt) OVER (ORDER BY dt, bus_id) AS cur, if(@t > 0, @t := cnt, @t := @t + cnt) AS cur_sum FROM ( SELECT bus_id, arrival_time AS dt, capacity AS cnt FROM Buses UNION ALL SELECT 1, arrival_time AS dt, 1 FROM Passengers ) AS a JOIN (SELECT @t := 0 x) AS b ) SELECT bus_id, if(cur_sum > 0, cnt  cur_sum, cnt) AS passengers_cnt FROM T WHERE bus_id > 0 ORDER BY bus_id;