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.
Top comments (0)