LC 1204 & LC 1205 & LC 1241

The following basic-level SQL problems.

LeetCode 1204

Last Person to Fit in the Bus (Medium) [link]

SELECT person_name
FROM(
    SELECT person_name, weight, 
        SUM(weight) OVER(ORDER BY turn) AS sum_weight
    FROM Queue
) t
WHERE sum_weight <= 1000
ORDER BY sum_weight DESC
LIMIT 1;

LeetCode 1205

Monthly Transactions II (Medium) [link]

SELECT month, country, 
    SUM(IF(tag = 1, 1, 0)) AS approved_count,
    SUM(IF(tag = 1, amount, 0)) AS approved_amount,
    SUM(IF(tag = 0, 1, 0)) AS chargeback_count,
    SUM(IF(tag = 0, amount, 0)) AS chargeback_amount
FROM(
    SELECT country, amount, 1 AS tag,
        DATE_FORMAT(trans_date, '%Y-%m') AS month
    FROM Transactions
    WHERE state = 'approved'
  
    UNION ALL
  
    SELECT country, amount, 0 AS tag,
        DATE_FORMAT(c.trans_date, '%Y-%m') AS month
    FROM Transactions t
    RIGHT JOIN Chargebacks c
    ON c.trans_id = t.id
) tmp
GROUP BY month, country;

LeetCode 1241

Number of Comments per Post (Easy) [link]

SELECT post_id, COUNT(sub_id) AS number_of_comments
FROM(
    SELECT DISTINCT s1.sub_id AS post_id, s2.sub_id 
    FROM Submissions s1
    LEFT JOIN Submissions s2
    ON s1.sub_id = s2.parent_id
    WHERE s1.parent_id IS NULL
) t
GROUP BY post_id
ORDER BY post_id;