LC 262 & LC 571 & LC 1369 & LC 1412

SQL-Advanced
SQL
Publish Date:   2022-04-18

The following advanced-level SQL problems.

LeetCode 262

Trips and Users (Hard) [link]

SELECT request_at AS Day, ROUND(SUM(IF(status != 'completed', 1, 0)) / COUNT(id),2) AS 'Cancellation Rate'
FROM(
    SELECT id, status, request_at
    FROM Trips
    LEFT JOIN Users u1
    ON Trips.client_id = u1.users_id
    LEFT JOIN Users u2
    ON Trips.driver_id = u2.users_id
    WHERE u1.banned = 'No' 
        AND u2.banned = 'No'
        AND (request_at BETWEEN '2013-10-01' AND '2013-10-03')
) t
GROUP BY request_at;

LeetCode 571

Median Employee Salary (Hard) [link]

Idea of find median: if both ascending cumulative sum and descending cumulative sum of frequencies are not less than half of total frequency, then we find the median number (if total freq is odd) or the two median number (if total freq is even).

SELECT ROUND(AVG(num),1) AS median
FROM(
    SELECT num, frequency,
        SUM(frequency) OVER(ORDER BY num) AS asc_freq,
        SUM(frequency) OVER(ORDER BY num DESC) AS desc_freq
    FROM Numbers
) t
WHERE asc_freq >= (SELECT SUM(frequency)/2 FROM Numbers) 
    AND desc_freq >= (SELECT SUM(frequency)/2 FROM Numbers);

LeetCode 1369

Get the Second Most Recent Activity (Hard) [link]

Idea: first remove the most recent activities from those who have more than 1 activities.

SELECT *
FROM UserActivity u1
where (u1.username, u1.startDate) IN (
    SELECT u.username, MAX(u.startDate)
    FROM UserActivity u
    WHERE (u.username, u.startDate) NOT IN (
        SELECT username, MAX(startDate)
        FROM UserActivity
        GROUP BY username
        HAVING COUNT(activity) > 1
    )
    GROUP BY u.username
);

LeetCode 1412

Find the quiet students in all exams (Hard) [link]

Idea: quiet students’ ascending rank and descending rank should not be 1 for all exam.

SELECT t.student_id, s.student_name
FROM(
    SELECT *,
        IF(dense_rank() OVER(PARTITION BY exam_id ORDER BY score) = 1, 1, 0) AS asc_rnk,
        IF(dense_rank() OVER(PARTITION BY exam_id ORDER BY score DESC) = 1, 1, 0) AS desc_rnk
    FROM Exam e
) t
LEFT JOIN Student s
ON s.student_id = t.student_id
GROUP BY t.student_id
HAVING SUM(t.asc_rnk) = 0 AND SUM(t.desc_rnk) = 0
ORDER BY t.student_id;
SQL-Advanced
  TOC