Not Boring Movies | LeetCode | MSSQL
Tags: leetcode, mssql, beginners, codenewbie
Published: 2023-05-30 17:31:00

## 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.

```sql
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.

![solution1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n84nh5jkgguod4u8vgy2.png)

### 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.

```sql
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.

![solution2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/slcqrogal9dog16exgbx.png)

### 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'.

```sql
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.

![solution3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b1gutea7mvzwilzx6s5p.png)

## 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](https://leetcode.com/problems/not-boring-movies/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}