Retiago Drago

Posted on

# Not Boring Movies | LeetCode | MSSQL

## The Problem

We have a `Cinema` table with the following schema:

id (PK) movie description rating
int varchar varchar float

The `id` field is a primary key. Each row contains information about a movie, its description, and its rating. We need to write an SQL query to report the movies with an odd-numbered ID and a description that doesn't include "boring". The result should be ordered by rating in descending order.

## Explanation

Here's an example:

Cinema table:

id movie description rating
1 War great 3D 8.9
2 Science fiction 8.5
3 irish boring 6.2
4 Ice song Fantacy 8.6
5 House card Interesting 9.1

Output:

id movie description rating
5 House card Interesting 9.1
1 War great 3D 8.9

There are three movies with odd-numbered IDs: 1, 3, and 5. The movie with ID = 3 has a description labeled as "boring", so we exclude it.

## The Solution

Let's review three SQL solutions, each utilizing a slightly different logic to reach the result.

### Source Code 1

The first solution checks for odd `id` values by using the modulus operator (`%`). It excludes rows with a description of 'boring' and sorts the output by `rating` in descending order.

``````SELECT
id,
movie,
description,
rating
FROM Cinema
WHERE
id%2 != 0
AND
description != 'boring'
ORDER BY
rating DESC
``````

This solution performs in 343ms, beating 83.24% of other submissions.

### Source Code 2

The second solution is similar to the first but uses a different method to check for odd IDs and uses `NOT LIKE` to check for 'boring' in the description.

``````SELECT
id,
movie,
description,
rating
FROM Cinema
WHERE
(id+1)%2 = 0
AND
description NOT LIKE '%boring%'
ORDER BY
rating DESC
``````

This solution takes 590ms, outperforming 17.90% of other solutions.

### Source Code 3

The third solution uses a `NOT` clause to negate conditions that we want to exclude, i.e., even IDs and descriptions equal to 'boring'.

``````SELECT
id,
movie,
description,
rating
FROM Cinema
WHERE NOT
(
id%2 = 0
OR
description = 'boring'
)
ORDER BY
rating DESC
``````

This solution has a runtime of 349ms, beating 79.36% of submissions.

## Conclusion

All solutions correctly provide the required output but have differing performances due to the varied logic utilized.

Here is the rank of the solutions from the best to worst for overall performance: Source Code 1 > Source Code 3 > Source Code 2.

You can find the original problem at LeetCode.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

## ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai