Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1050.html
1050. Customer Placing the Largest Number of Orders
Level
Easy
Description
Table: ActorDirector
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+-------------+---------+
timestamp is the primary key column for this table.
Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor have cooperated with the director at least 3 times.
Example:
ActorDirector table:
+-------------+-------------+-------------+
| actor_id | director_id | timestamp |
+-------------+-------------+-------------+
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
| 2 | 1 | 6 |
+-------------+-------------+-------------+
Result table:
+-------------+-------------+
| actor_id | director_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
The only pair is (1, 1) where they cooperated exactly 3 times.
Solution
Select entries with field actor_id and director_id from table ActorDirector. Since only the entries where the actor have cooperated with the director at least 3 times, the entries should be grouped by actor_id and director_id, and the count should be at least 3.
# Write your MySQL query statement below
select actor_id, director_id from ActorDirector
group by actor_id, director_id
having count(actor_id) >= 3;