JEET Dhanesha

Friendly Movies Streamed Last Month

,

View Problem

Problem Statement

Write a solution to report the distinct titles of the kid-friendly movies streamed in June 2020.

Return the result table in any order.

Input:

TVProgram table:
+--------------------+--------------+-------------+
| program_date       | content_id   | channel     |
+--------------------+--------------+-------------+
| 2020-06-10 08:00   | 1            | LC-Channel  |
| 2020-05-11 12:00   | 2            | LC-Channel  |
| 2020-05-12 12:00   | 3            | LC-Channel  |
| 2020-05-13 14:00   | 4            | Disney Ch   |
| 2020-06-18 14:00   | 4            | Disney Ch   |
| 2020-07-15 16:00   | 5            | Disney Ch   |
+--------------------+--------------+-------------+
Content table:
+------------+----------------+---------------+---------------+
| content_id | title          | Kids_content  | content_type  |
+------------+----------------+---------------+---------------+
| 1          | Leetcode Movie | N             | Movies        |
| 2          | Alg. for Kids  | Y             | Series        |
| 3          | Database Sols  | N             | Series        |
| 4          | Aladdin        | Y             | Movies        |
| 5          | Cinderella     | Y             | Movies        |
+------------+----------------+---------------+---------------+

Output

+--------------+
| title        |
+--------------+
| Aladdin      |
+--------------+

Solution – 1

select distinct C.title 
from TVProgram T 
inner join Content C  
on T.content_id = C.content_id 
where date_format(T.program_date, "%Y-%m") = "2020-06" and 
C.Kids_content = "Y" and C.content_type = "Movies";

Solution – 2

select distinct C.title 
from TVProgram T 
inner join (
    select * 
    from Content 
    where Kids_content = "Y" and content_type = "Movies"
) as C  
on T.content_id = C.content_id 
where date_format(T.program_date, "%Y-%m") = "2020-06";

Trending