[리트코드/SQL] 31. Actors and Directors Who Cooperated At Least Three Times (1050)

👉https://leetcode.com/problems/actors-and-directors-who-cooperated-at-least-three-times/description/

Actors and Directors Who Cooperated At Least Three Times - LeetCode

---

🤔 문제 분석

1) Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times.

 

💡 풀이

SELECT actor_id, director_id
FROM (SELECT actor_id, director_id, count(timestamp) AS time
      FROM ActorDirector
      GROUP BY actor_id, director_id) AS times
WHERE time >= 3

having절에 파생변수(?) 사용이 불가능해서 서브쿼리를 활용해서 풀었지만..

사실 아래와 같이 풀면 더 간단하게 풀 수 있는 문제였다..

SELECT actor_id, director_id, count(timestamp) AS time
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*) >= 3