SQL Interview Questions – Leetcode 620 – Not Boring Movies

Problem Description –

Write an SQL query to report the movies with an odd-numbered ID and a description that is not "boring".

Return the result table in descending order by rating.

The query result format is in the following example:

Difficulty Level – Easy

Solution –

SELECT
    id,
    movie,
    description,
    rating
FROM (
SELECT *,
    CASE WHEN MOD(id,2) != 0 THEN 1 ELSE 0 END as flag
FROM cinema
    ) t1
WHERE flag = 1 AND description != 'boring'
ORDER BY rating DESC

The decide which id’s are odd, we are using the MOD() function. This function returns the remainder of a number divided by another number. So, if we divide an odd-numbered id by 2 then its remainder is not going to be 0 that is why we used the condition and assign the value of 1, and in the case of an even number, we assign a value of 0 in the flag column. Now, in the outer query, all we are doing is just selecting the rows where flag = 1 and description!= ‘boring’ to get the desired result. Also, we have to order the result by rating in descending order.