Patterns For Solving LeetCode [Med — Hard] Problems

Pattern 1: Select consecutive number

https://leetcode.com/problems/consecutive-available-seats/

Use ABS() to select consecutive numbers:

ABS(a.seat_id — b.seat_id)=1

SELECT DISTINCT
    a.seat_id
    FROM cinema a
    JOIN cinema b
    ON ABS(a.seat_id - b.seat_id)=1
    WHERE a.free = 1 and b.free=1
    ORDER BY a.seat_id

Pattern 2: Swap Two Rows

https://leetcode.com/problems/exchange-seats/

Because we are swapping two rows, we can utilize SQL function mod() to select the id of the rows we want to swap:

CASE WHEN MOD(id,2)=1 and id != (SELECT count(*) FROM seat) THEN id+1

SELECT
CASE WHEN MOD(id,2)=1 and id != (SELECT count(*) FROM seat) THEN id+1
     WHEN MOD(id,2)=0 THEN id-1
     ELSE id END id, student
FROM seat
ORDER BY id

Pattern 3. Use DATEDIFF() to search for date range

https://leetcode.com/problems/user-activity-for-the-past-30-days-ii/

With Temp as (
    select count(distinct session_id ) as num
    from Activity 
    where datediff('2019-07-27',activity_date) <=29
    group by user_id 
)
Select IFNULL( ROUND(AVG(num),2), 0.00) as "average_sessions_per_user"
from Temp;

Pattern 4: Find consecutive intervals

To transform the original table, we can take these steps to construct a table like the following:

• Find the consecutive increment number for each row: [1,2,3,4,5,6,7] using the ROW_NUMBER() window function
• Subtract the consecutive increment numbers from the NUM column to get the Diff column
• Use the Group By function to pivot different intervals (represented by the Diff column)
• Use the min & max function to find the start and end of each grouped interval (e.g: we have 3 groups (0,3,4), and for group ‘0’ , we have these nums [1,2,3], and we can simply to min(nums) to get 1, which is the smallest (beginning) of the number array

WITH temp as (
SELECT
log_id, RANK() OVER(ORDER BY log_id asc) as rnk 
FROM Logs)
SELECT 
min(log_id) as start_id, max(log_id) as end_id
FROM
temp
GROUP BY
log_id - rnk

Pattern 5: Use Regex to match string patterns

https://leetcode.com/problems/find-users-with-valid-e-mails/

Further information for using Regex in SQL query https://dataschool.com/how-to-teach-people-sql/how-regex-works-in-sql/

SELECT 
* from Users 
where mail regexp '^[A-Za-z]{1}[A-Za-z0-9\\.\\-_]*@leetcode.com$'

Pattern 6: Use ROW_NUMBER() to calculate median

• We can use the row_number() function to assign rank for each value in the array
• The trick is to sort the array by both ascending & descending order, and find the rows where the ascending index -1 ≤ the descending index ≤ the ascending index (see table below for better representation)

WITH temp as (SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Company Order by Salary ASC, Id asc) rnkAsc,
ROW_NUMBER() OVER (PARTITION BY Company Order by Salary DESC, id desc) rnkDesc
FROM
Employee)
SELECT
Id,
Company,
Salary
FROM temp
WHERE rnkAsc BETWEEN rnkDesc-1 and rnkDesc+1
ORDER BY Company, Salary

Pattern 7: Extract contiguous date intervals for specific timeframes

https://leetcode.com/problems/report-contiguous-dates/

• We were tasked to extract the number of days the fall into each of 2018, 2019, 2020 bucket

• Hence we need a way to extract the number of days; in this case, we first processed the rows having either period_start OR period_end in a particular year
• Next, let’s use the logic below to extract the days

DATEDIFF(LEAST(period_end, ‘2020–12–31’), GREATEST(period_start, ‘2020–01–01’))+1

• For example, we have this range 2019–12–01 ~ 2020–01–31
• this row will be picked up by both of these WHERE clause:

WHERE YEAR(period_start)=2019 OR YEAR(period_end)=2019, and

WHERE YEAR(period_start)=2020 OR YEAR(period_end)=2020

• Referring back to our logic above:

SELECT
SQ1.product_id, product_name, report_year, sum(total_amount) total_amount
FROM
 ( SELECT product_id, ‘2018’ AS report_year,
 average_daily_sales * (DATEDIFF(LEAST(period_end, ‘2018–12–31’), GREATEST(period_start, ‘2018–01–01’))+1) AS total_amount
 FROM Sales
 WHERE YEAR(period_start)=2018 OR YEAR(period_end)=2018
UNION ALL
SELECT product_id, '2019' AS report_year,
        average_daily_sales * (DATEDIFF(LEAST(period_end, '2019-12-31'), GREATEST(period_start, '2019-01-01'))+1) AS total_amount
    FROM Sales
    WHERE YEAR(period_start)<=2019 AND YEAR(period_end)>=2019
UNION ALL
SELECT product_id, '2020' AS report_year,
        average_daily_sales * (DATEDIFF(LEAST(period_end, '2020-12-31'), GREATEST(period_start, '2020-01-01'))+1) AS total_amount
    FROM Sales
    WHERE YEAR(period_start)=2020 OR YEAR(period_end)=2020) SQ1
    LEFT JOIN Product
    ON SQ1.product_id = Product.product_id
    GROUP BY SQ1.product_id, report_year
    ORDER BY SQ1.product_id ASC