LC 2153 & LC 2173 & LC 2199

The following advanced-level SQL problems.

LeetCode 2153*

The Number of Passengers in Each Bus II (Hard) [link]

Idea:

• We define a new variable left_passengers_num. If it’s a negative number, that means there remains empty seats. If it’s a positive number, that means the number of passengers waiting at the station.
• We use bus_id = -1 for passengers because we want to sort the passengers and buses by arrival time and keep passengers at the beginning.
• Eventually, we use left_passengers_num - capacity to calculate the number of passengers getting on to the bus for the bus still has empty seats. And use -capacity to calculate the number of passengers getting on to the bus for the bus that is full and not be able to take all passengers staying at the station.

SELECT bus_id, 
    IF(left_passengers_num < 0, left_passengers_num - capacity, -capacity) AS passengers_cnt
FROM (
    SELECT bus_id, capacity, 
        IF(@left_passengers_num < 0, @left_passengers_num := capacity, @left_passengers_num := @left_passengers_num + capacity) AS left_passengers_num,
        COUNT(1) OVER() nums # provide index for left_passengers_num
    FROM (
        SELECT bus_id, arrival_time, -capacity capacity 
        FROM Buses 
        UNION ALL
        SELECT -1 bus_id, arrival_time, 1 capacity 
        FROM Passengers
    ) T1, (SELECT @left_passengers_num := 0) T2
    ORDER BY arrival_time, bus_id
) T
WHERE bus_id > 0
ORDER BY bus_id;

LeetCode 2173

Longest Winning Streak (Hard) [link]

Ideas:

Determine whether the current result is the same as the previous result, use LAG OVER window function.

Use SUM OVER to make groups to make sure discontinued wins are not in the same group.

SELECT player_id, MAX(IF(result = 'Win', cnt, 0)) AS longest_streak
FROM(
    SELECT *, COUNT(player_id) AS cnt
    FROM(
        SELECT *, 
            SUM(is_continuity) OVER (PARTITION BY player_id ORDER BY match_day) AS logic_group
        FROM(
            SELECT *, 
                IF(result != LAG(result) OVER (PARTITION BY player_id ORDER BY match_day), 1, 0) AS is_continuity
            FROM Matches
        ) t
    ) tt
    GROUP BY player_id, logic_group, result
) ttt
GROUP BY player_id
ORDER BY player_id;

LeetCode 2199

Finding the Topic of Each Post (Hard) [link]

The INSTR() function returns the position of the first occurrence of a string in another string. It’s a case-insensitive search.

GROUP_CONCAT to concat strings by column by group.

WITH t AS (
    SELECT DISTINCT post_id,topic_id 
    FROM Keywords,Posts 
    WHERE INSTR(CONCAT(' ',content,' '),CONCAT(' ',word,' '))>0
)

SELECT post_id, GROUP_CONCAT(topic_id ORDER BY topic_id,'') topic 
FROM t 
GROUP BY post_id
UNION
SELECT P.post_id,'Ambiguous!' 
FROM Posts P 
LEFT JOIN t 
ON P.post_id = t.post_id 
GROUP BY P.post_id 
HAVING COUNT(t.topic_id)=0;