Not Boring Movies

Difficulty: Easy | Acceptance: 75.10% | Paid: No Topics: Database

X city opened a new cinema. Many people want to go to this cinema. The cinema can give a discount to some people. The discount is based on the price of the ticket and the review of the movie.

Table: Cinema

+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | movie | varchar | | description | varchar | | rating | float | +---------------+---------+ id is the primary key (column with unique values) for this table. Each row contains information about the name of a movie, its genre, and its rating. rating is a floating point number.

Write a solution to report the movies with an odd-numbered ID and a description that is not “boring”. Return the result table ordered by rating in descending order.

The result format is in the following example.

• Examples
• Constraints
• Basic SQL Filtering
• Using Modulo Operator
• Using Bitwise AND

Examples

Example 1:

Input: Cinema table: +----+------------+-------------+--------+ | id | movie | description | rating | +----+------------+-------------+--------+ | 1 | War | great 3D | 8.9 | | 2 | Science | fiction 8.9 | 8.5 | | 3 | amazing | movie | 8.0 | | 4 | forever | song | 7.2 | | 5 | fun | journey | 7.5 | | 6 | boring | not | 6.1 | | 7 | space | force | 6.9 | | 8 | mystery | movie | 6.1 | +----+------------+-------------+--------+ Output: +----+------------+-------------+--------+ | id | movie | description | rating | +----+------------+-------------+--------+ | 5 | fun | journey | 7.5 | | 1 | War | great 3D | 8.9 | | 3 | amazing | movie | 8.0 | | 7 | space | force | 6.9 | +----+------------+-------------+--------+ Explanation: Only movies with odd id (1, 3, 5, 7) and description not containing “boring” are included. The result is ordered by rating in descending order.

Constraints

The number of rows in the table is in the range [1, 10⁴].
The rating values are in the range [0, 10].

Basic SQL Filtering

Intuition Filter rows by odd ID and non-boring description, then sort by rating descending.

Steps

• Select all columns from Cinema table
• Filter where id is odd (id % 2 = 1)
• Filter where description is not ‘boring’
• Order by rating in descending order

SELECT * FROM Cinema WHERE id % 2 = 1 AND description != 'boring' ORDER BY rating DESC

Complexity

• Time: O(n log n) where n is the number of rows (due to sorting)
• Space: O(k) where k is the number of matching rows
• Notes: Standard SQL approach with WHERE clause filtering and ORDER BY sorting

Using Modulo Operator

Intuition Use the MOD function instead of % operator for checking odd numbers.

Steps

• Select all columns from Cinema table
• Filter where MOD(id, 2) = 1 (odd number check)
• Filter where description is not ‘boring’
• Order by rating in descending order

SELECT * FROM Cinema WHERE MOD(id, 2) = 1 AND description <> 'boring' ORDER BY rating DESC

Complexity

• Time: O(n log n) where n is the number of rows (due to sorting)
• Space: O(k) where k is the number of matching rows
• Notes: MOD function is more portable across different SQL dialects

Using Bitwise AND

Intuition Use bitwise AND operation to check if the least significant bit is set (indicating odd number).

Steps

• Select all columns from Cinema table
• Filter where id & 1 = 1 (bitwise check for odd)
• Filter where description is not ‘boring’
• Order by rating in descending order

SELECT * FROM Cinema WHERE id & 1 = 1 AND description != 'boring' ORDER BY rating DESC

Complexity

• Time: O(n log n) where n is the number of rows (due to sorting)
• Space: O(k) where k is the number of matching rows
• Notes: Bitwise operations can be slightly faster than modulo in some databases