有人知道谷歌BIE的面试吗？考什么，怎么考？

# WHERE - 2

620. Not Boring Movies (Where MOD())

1. SELECT * FROM cinema WHERE description <> 'boring' AND MOD(id,2) <> 0 ORDER BY 4 DESC

复制代码

1757. Recyclable and Low Fat Products (WHERE)

1. SELECT product_id FROM Products WHERE low_fats = 'Y' AND recyclable = 'Y' ORDER BY 1 ASC

复制代码

# LEAD() and LAG() - 1

603. Consecutive Available Seats (LEAD() and LAG())

https://leetcode.com/problems/co ... L-Solutions%3A-LEAD()-and-Self-Join-%2B-Union

1. SELECT seat_id
   
2. FROM ( SELECT
   
3.          CASE
   
4.            WHEN seat_id + 1 = IFNULL(LEAD(seat_id) OVER(ORDER BY seat_id),-100) THEN seat_id
   
5.            WHEN seat_id - 1 = IFNULL(LAG(seat_id) OVER(ORDER BY seat_id),-100) THEN seat_id
   
6.          END AS seat_id
   
7.        FROM cinema WHERE free = 1 ) T
   
8. WHERE seat_id IS NOT NULL
   
9. ORDER BY 1 ASC
   
10. WITH CTE AS (SELECT * FROM cinema WHERE free = 1 )
    
11. 
    
12. SELECT C1.seat_id FROM CTE C1
    
13. LEFT JOIN CTE C2 ON C1.seat_id = C2.seat_id + 1
    
14. LEFT JOIN CTE C3 ON C1.seat_id = C3.seat_id - 1
    
15. WHERE C2.seat_id IS NOT NULL OR C3.seat_id IS NOT NULL
    
16. ORDER BY 1 ASC
    
17. WITH STEP1 AS
    
18. (SELECT * FROM cinema WHERE free = 1)
    
19. 
    
20. SELECT S1.seat_id FROM STEP1 S1
    
21. INNER JOIN STEP1 S2 ON S1.seat_id = S2.seat_id - 1
    
22. 
    
23. UNION
    
24. 
    
25. SELECT S1.seat_id FROM STEP1 S1
    
26. INNER JOIN STEP1 S2 ON S1.seat_id = S2.seat_id + 1

复制代码