Question

1495. Friendly Movies Streamed Last Month

中文文档

Description

Table: TVProgram

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| program_date  | date  |
| content_id  | int   |
| channel     | varchar |
+---------------+---------+
(program_date, content_id) is the primary key (combination of columns with unique values) for this table.
This table contains information of the programs on the TV.
content_id is the id of the program in some channel on the TV.

 

Table: Content

+------------------+---------+
| Column Name    | Type  |
+------------------+---------+
| content_id     | varchar |
| title      | varchar |
| Kids_content   | enum  |
| content_type   | varchar |
+------------------+---------+
content_id is the primary key (column with unique values) for this table.
Kids_content is an ENUM (category) of types ('Y', 'N') where: 
'Y' means is content for kids otherwise 'N' is not content for kids.
content_type is the category of the content as movies, series, etc.

 

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

Return the result table in any order.

The result format is in the following example.

 

Example 1:

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    |
+--------------+
Explanation: 
"Leetcode Movie" is not a content for kids.
"Alg. for Kids" is not a movie.
"Database Sols" is not a movie
"Alladin" is a movie, content for kids and was streamed in June 2020.
"Cinderella" was not streamed in June 2020.

Solutions

Solution 1: Equi-Join + Conditional Filtering

We can first use an equi-join to join the two tables based on the content_id field, and then use conditional filtering to select the child-friendly movies that were played in June 2020.

# Write your MySQL query statement below
SELECT DISTINCT title
FROM
  TVProgram
  JOIN Content USING (content_id)
WHERE
  DATE_FORMAT(program_date, '%Y%m') = '202006'
  AND kids_content = 'Y'
  AND content_type = 'Movies';