LC 1127 & LC 1635 & LC 1767

The following advanced-level SQL problems are about ‘Recursion, Dependency, Nesting’. Focusing on creating new tables.

LeetCode 1127

User Purchase Platform (Hard) [link]

SELECT t1.spend_date, t1.platform,
    IFNULL(SUM(t2.amount), 0) AS total_amount,
    IFNULL(COUNT(t2.user_id), 0) AS total_users
FROM (
    SELECT DISTINCT spend_date, 'desktop' as platform FROM Spending
    UNION
    SELECT DISTINCT spend_date, 'mobile' as platform FROM Spending
    UNION
    SELECT DISTINCT spend_date, 'both' as platform FROM Spending
) t1
LEFT JOIN (
    SELECT user_id, spend_date,
        IF(COUNT(platform) = 2, 'both', platform) AS platform,
        SUM(amount) AS amount
    FROM Spending
    GROUP BY user_id, spend_date
) t2
ON t1.spend_date = t2.spend_date AND t1.platform = t2.platform
GROUP BY 1,2;

LeetCode 1635

Hopper Company Queries I (Hard) [link]

with recursive tmp(month) as (
    SELECT 1
    UNION ALL
    SELECT month+1
    FROM tmp
    WHERE month <= 11
)

SELECT
    t1.month, IFNULL(t1.active_drivers,0) AS active_drivers, IFNULL(t2.accepted_rides, 0) AS accepted_rides
FROM (
    SELECT tmp.month, COUNT(d.driver_id) AS active_drivers
    FROM tmp
    LEFT JOIN Drivers d
    ON '202000' + tmp.month >= DATE_FORMAT(d.join_date, '%Y%m')
    GROUP BY tmp.month
) t1
LEFT JOIN (
    SELECT MONTH(r.requested_at) AS month, r.ride_id, COUNT(r.ride_id) AS accepted_rides
    FROM Rides r
    JOIN AcceptedRides a
    ON r.ride_id = a.ride_id
    WHERE YEAR(r.requested_at) = '2020'
    GROUP BY 1
) t2
ON t1.month = t2.month;

LeetCode 1767

Find the subtasks that did not execute (Hard) [link]

with recursive tmp(task_id, subtask_id) as (
    SELECT task_id, subtasks_count FROM Tasks
    UNION ALL
    SELECT task_id, subtask_id - 1
    FROM tmp
    WHERE subtask_id - 1 > 0
)

SELECT tmp.task_id, tmp.subtask_id
FROM tmp
LEFT JOIN Executed e
ON tmp.task_id = e.task_id AND tmp.subtask_id = e.subtask_id
WHERE e.subtask_id IS NULL;