[LeetCode] SQL 50 : Select

하루에 5문제 이상씩 풀 예정

 

# 1757. Recyclable and Low Fat Products

https://leetcode.com/problems/recyclable-and-low-fat-products/?envType=study-plan-v2&envId=top-sql-50

Recyclable and Low Fat Products - LeetCode

SELECT product_id
FROM products
WHERE low_fats = 'Y'
    AND recyclable = 'Y'

# 584. Find Customer Referee

https://leetcode.com/problems/find-customer-referee/?envType=study-plan-v2&envId=top-sql-50

Find Customer Referee - LeetCode

SELECT name
FROM customer
WHERE referee_id != 2
    OR referee_id IS NULL

 

# 595. Big Countries

https://leetcode.com/problems/big-countries/?envType=study-plan-v2&envId=top-sql-50

Big Countries - LeetCode

SELECT name, population, area
FROM world
WHERE population >= 25000000
    OR area >= 3000000

 

# 1148. Article Views 1

https://leetcode.com/problems/article-views-i/?envType=study-plan-v2&envId=top-sql-50

Article Views I - LeetCode

SELECT id
FROM (
    SELECT author_id AS id,
        COUNT(CASE WHEN author_id = viewer_id THEN viewer_id END) AS cnt
    FROM views
    GROUP BY id
    HAVING cnt >= 1
    ORDER BY id
) AS a

CASE문 작성하고 END로 마무리하는거 계속 까먹음

굳이 서브쿼리까지 안써도 될거같아서 다른 풀이도 찾아봤다

SELECT DISTINCT author_id AS id
FROM views
WHERE author_id = viewer_id
ORDER BY id

WHERE절로 원하는 조건을 걸어주고 SELECT DISTINCT로 조건에 맞는 author_id를 1개만 뽑으면 됨

근데 막상 속도는 위의 FROM절 서브쿼리 작성한게 더 빨랐다!

 

# 1683. Invalid Tweets

https://leetcode.com/problems/invalid-tweets/?envType=study-plan-v2&envId=top-sql-50

Invalid Tweets - LeetCode

SELECT tweet_id
FROM tweets
WHERE CHAR_LENGTH(content) > 15