Not Boring Movies | LeetCode 620 | Crack SQL Interviews in 50 Qs

Welcome to the transition into Basic Aggregate Functions and Conditional Filtering in SQL. In this chapter, we explore LeetCode 620 ("Not Boring Movies"), an approachable yet foundational problem that tests multiple core SQL clauses: table selection, modulo arithmetic for odd/even detection, string inequality filtering, logical conjunctions, and sorting data sets in descending order.

1. Why Do We Need Conditional Filtering & Ordering?

When querying relational databases in real-world applications (such as movie streaming recommendations, e-commerce inventories, or user dashboards), raw data dumps are rarely useful.

• The Status Quo: A table might contain millions of rows covering every imaginable attribute.
• The Problem: We need a way to narrow down rows dynamically based on business logic (e.g., retrieving movies with odd IDs that are not marked as boring) and presenting them sorted by user preference (e.g., highest rating first).
• The Solution: We combine the WHERE clause for multi-condition filtering with the ORDER BY clause for sorting.

2. Core Schema & Database Layout

The problem introduces a single table named cinema containing structural movie attributes.

Real-Life Analogy

Mental Hook: Imagine walking into a video rental store where the manager hands you a strict checklist. You must scan the catalog and pick only movies where the shelf number is an odd digit (1, 3, 5, etc.), throw away any movie explicitly labeled as "boring" on its back cover, and finally stack them on the counter from highest rating to lowest rating so you can pick the best one immediately.

3. Breaking Down the Business Rules

To solve LeetCode 620 successfully, we must satisfy three distinct operational constraints simultaneously:

1. All Columns Return: The output table must return all four attributes (id, movie, description, rating).
2. Dual Filtering Criteria (WHERE):
   • Odd ID Rule: The movie's ID must be an odd number.
   • Exclusion Rule: The description column must not contain the string 'boring'.
3. Sorting Constraint (ORDER BY):
   • The final result set must be ordered by rating in descending order (highest scores first).

4. Mathematical Modulo Operation for Odd/Even Detection

How do we check if an integer is odd in SQL? We use the Modulo operator (%), which calculates the remainder of a division operation.

• If an integer is divided by 2 and the remainder is 0, the number is even ($n \pmod 2 = 0$).
• If an integer is divided by 2 and the remainder is not 0, the number is odd ($n \pmod 2 \neq 0$).

Mathematical Representation

For any movie identifier id:

5. Naive vs. Production-Ready SQL Query Structure

Let's look at how we build our query iteratively.

Naive / Incomplete Approach

-- Fails to handle ordering and misses strict condition combinations
SELECT * 
FROM cinema 
WHERE description != 'boring';

Production-Ready Query

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

Alternatively, using the wildcard * shorthand (since all table columns are requested):

SELECT *
FROM cinema
WHERE id % 2 <> 0 
  AND description <> 'boring'
ORDER BY rating DESC;

Note: Both != and <> are standard SQL operators representing "not equal to".

6. Step-by-Step Flow Walkthrough

Let's trace how the database engine executes our production-ready query step by step:

7. Comparative SQL Operators Reference

8. Summary and Key Takeaways

• Primary Key Integrity: The id column uniquely identifies each row, serving as our reliable indexing mechanism.
• Conditional Filtering: Use the WHERE clause to filter rows before aggregation or projection occurs.
• Modulo Arithmetic: Use id % 2 != 0 to accurately isolate odd-numbered records.
• String Comparisons: Use string literals in quotes (e.g., 'boring') combined with != or <> to exclude specific categorical text.
• Combining Conditions: Use the AND logical operator to enforce multiple constraints simultaneously.
• Sorting Direction: Remember that ORDER BY defaults to ascending order (ASC); you must explicitly append DESC for descending order.
• Table Projections: Use SELECT * when all table attributes are required in the final output report.