LC 180 & LC 1459 & LC 1988

SQL-Basic
SQL
Publish Date:   2022-03-29

The following basic-level SQL problems are about ‘inequality linking’.

LeetCode 180

Consecutive Numbers (Medium) [link]

The initial thought might be grouping by num and count the number of same num, but this approach cannot detect consecutive numbers, so the count is not the count of consecutive numbers. To make sure we are counting the consecutive numbers, we need to group by one more variable - which is rnk (consecutive num would have same rnk).

We need row_number() over(order by id) because the id may not be sorted. If the rnk is the same, then the numbers are consecutive with continuous ids. 

SELECT DISTINCT t.num AS ConsecutiveNums
FROM(
    SELECT *, 
        row_number() OVER(ORDER BY id) - row_number() OVER(PARTITION BY num ORDER BY id) AS rnk
        # id - row_number() OVER(PARTITION BY num) AS rnk  --- THIS DOES NOT WORK
    FROM Logs
) AS t
GROUP BY t.rnk, t.num
HAVING COUNT(t.num) >= 3;

LeetCode 1459

Rectangles Area (Medium) [link]

SELECT p1.id AS p1, p2.id AS p2, 
    abs(p1.y_value - p2.y_value) * abs(p1.x_value - p2.x_value) AS area
FROM Points p1
CROSS JOIN Points p2
WHERE p1.id < p2.id 
    AND p1.x_value != p2.x_value 
    AND p1.y_value != p2.y_value
ORDER BY area DESC, p1 ASC, p2 ASC;

LeetCode 1988

Find Cutoff Score for Each School (Medium) [link]

Note that the student counts in table Exam are already the cumulative count.

SELECT t.school_id, IFNULL(MIN(t.score), -1) AS score
FROM (
    SELECT S.school_id, E.score AS score
    FROM Schools S
    LEFT JOIN Exam E
    ON E.student_count <= S.capacity
) AS t
GROUP BY t.school_id;
SQL-Basic
  TOC