MIN and MAX in SQLite

---

SETUP — Table Used

SELECT * FROM netflix_titles LIMIT 5;
PRAGMA table_info(netflix_titles);

Assume netflix_titles has: title_id, title, genre, rating, imdb_score, release_year, duration_mins, is_original, is_documentary, is_canceled, country, language

---

PART 1 — BASICS

---

Syntax

MIN(column)   -- lowest value in the column
MAX(column)   -- highest value in the column

-- Both:
--   ignore NULL values automatically
--   work on numbers, text (alphabetical), and dates

---

1. Basic MIN and MAX

-- Score range across all titles
SELECT
    MIN(imdb_score)   AS lowest_score,
    MAX(imdb_score)   AS highest_score
FROM netflix_titles;

-- Duration range
SELECT
    MIN(duration_mins) AS shortest,
    MAX(duration_mins) AS longest
FROM netflix_titles;

-- Release year range
SELECT
    MIN(release_year)  AS oldest,
    MAX(release_year)  AS newest
FROM netflix_titles;

---

2. MIN and MAX on Text — alphabetical order

-- First and last title alphabetically
SELECT
    MIN(title) AS first_alphabetically,
    MAX(title) AS last_alphabetically
FROM netflix_titles;

-- First and last country alphabetically
SELECT
    MIN(country) AS first_country,
    MAX(country) AS last_country
FROM netflix_titles;

MIN on text returns the value that comes first alphabetically. MAX returns the last. NULLs are ignored.

---

3. MIN and MAX with WHERE

-- Score range for Netflix Originals only
SELECT
    MIN(imdb_score) AS min_score,
    MAX(imdb_score) AS max_score
FROM netflix_titles
WHERE is_original = 'Y';

-- Duration range for Documentaries
SELECT
    MIN(duration_mins) AS shortest_doc,
    MAX(duration_mins) AS longest_doc
FROM netflix_titles
WHERE is_documentary = 'Y';

-- Score range for titles released after 2019
SELECT
    MIN(imdb_score) AS min_score,
    MAX(imdb_score) AS max_score
FROM netflix_titles
WHERE release_year > 2019;

---

PART 2 — MIN / MAX WITH GROUP BY

---

4. MIN and MAX per Group

-- Score range per genre
SELECT
    genre,
    MIN(imdb_score)   AS lowest_score,
    MAX(imdb_score)   AS highest_score
FROM netflix_titles
GROUP BY genre
ORDER BY highest_score DESC;

Result:

genre	lowest_score	highest_score
Drama	5.1	9.3
Documentary	6.2	9.1
Comedy	4.8	8.7

---

5. Full Stats per Group — MIN, MAX, AVG, COUNT together

-- Complete score breakdown per genre
SELECT
    genre,
    COUNT(*)                    AS total_titles,
    MIN(imdb_score)             AS min_score,
    MAX(imdb_score)             AS max_score,
    ROUND(AVG(imdb_score), 2)   AS avg_score,
    MAX(imdb_score)
        - MIN(imdb_score)       AS score_range
FROM netflix_titles
GROUP BY genre
ORDER BY avg_score DESC;

---

6. MIN and MAX release year per genre — useful for spans

-- When did each genre first and last appear on the platform?
SELECT
    genre,
    MIN(release_year)  AS first_release,
    MAX(release_year)  AS latest_release,
    MAX(release_year)
        - MIN(release_year) AS year_span
FROM netflix_titles
GROUP BY genre
ORDER BY first_release;

---

7. HAVING with MIN / MAX — filter groups by range

-- Only genres where the best title scores above 8.5
SELECT
    genre,
    MAX(imdb_score) AS best_score
FROM netflix_titles
GROUP BY genre
HAVING MAX(imdb_score) > 8.5
ORDER BY best_score DESC;

-- Genres where even the worst title scores above 6.0
SELECT
    genre,
    MIN(imdb_score) AS worst_score
FROM netflix_titles
GROUP BY genre
HAVING MIN(imdb_score) > 6.0
ORDER BY worst_score DESC;

-- Genres with a wide score range (inconsistent quality)
SELECT
    genre,
    MIN(imdb_score)                AS min_score,
    MAX(imdb_score)                AS max_score,
    MAX(imdb_score) - MIN(imdb_score) AS score_range
FROM netflix_titles
GROUP BY genre
HAVING MAX(imdb_score) - MIN(imdb_score) > 3.0
ORDER BY score_range DESC;

---

PART 3 — FINDING THE ROW WITH MIN / MAX VALUE

---

8. Retrieve the full row for the highest / lowest value

-- Title with the highest IMDb score
SELECT title, genre, imdb_score
FROM netflix_titles
WHERE imdb_score = (SELECT MAX(imdb_score) FROM netflix_titles);

-- Title with the shortest duration
SELECT title, genre, duration_mins
FROM netflix_titles
WHERE duration_mins = (SELECT MIN(duration_mins) FROM netflix_titles);

-- Most recently released title
SELECT title, genre, release_year
FROM netflix_titles
WHERE release_year = (SELECT MAX(release_year) FROM netflix_titles);

This is the standard SQLite pattern for "get the row with the max value". SQLite does not support SELECT TOP 1 — use a subquery or ORDER BY + LIMIT.

---

9. ORDER BY + LIMIT — alternative approach

-- Highest rated title
SELECT title, genre, imdb_score
FROM netflix_titles
ORDER BY imdb_score DESC
LIMIT 1;

-- Lowest rated title
SELECT title, genre, imdb_score
FROM netflix_titles
ORDER BY imdb_score ASC
LIMIT 1;

-- Top 5 longest titles
SELECT title, genre, duration_mins
FROM netflix_titles
ORDER BY duration_mins DESC
LIMIT 5;

Subquery approach  →  returns ALL rows tied for the max value
LIMIT 1 approach   →  returns exactly 1 row (arbitrary if tied)
Use subquery when ties matter; LIMIT 1 when you just need any top result.

---

10. Best title per genre — MIN/MAX + subquery

-- Highest rated title in each genre
SELECT title, genre, imdb_score
FROM netflix_titles t
WHERE imdb_score = (
    SELECT MAX(imdb_score)
    FROM netflix_titles
    WHERE genre = t.genre      -- correlated: matches outer row's genre
)
ORDER BY genre;

---

11. Best title per genre — CTE + ROW_NUMBER (cleaner)

-- Highest rated title per genre using CTE
WITH ranked AS (
    SELECT
        title,
        genre,
        imdb_score,
        ROW_NUMBER() OVER (PARTITION BY genre ORDER BY imdb_score DESC) AS rn
    FROM netflix_titles
)
SELECT title, genre, imdb_score
FROM ranked
WHERE rn = 1
ORDER BY genre;

---

PART 4 — MIN / MAX WITH CASE

---

12. Conditional MIN / MAX — within a subset

-- Best score among originals vs licensed per genre
SELECT
    genre,
    MAX(CASE WHEN is_original = 'Y' THEN imdb_score END) AS best_original,
    MAX(CASE WHEN is_original = 'N' THEN imdb_score END) AS best_licensed,
    MIN(CASE WHEN is_original = 'Y' THEN imdb_score END) AS worst_original,
    MIN(CASE WHEN is_original = 'N' THEN imdb_score END) AS worst_licensed
FROM netflix_titles
GROUP BY genre
ORDER BY genre;

Result:

genre	best_original	best_licensed	worst_original	worst_licensed
Drama	9.3	8.7	5.8	5.1
Comedy	8.7	8.1	5.2	4.8

---

13. MIN / MAX with CASE — pick value based on condition

-- Effective score: penalize canceled titles by capping their score at 6.0
SELECT
    title,
    imdb_score,
    MIN(imdb_score,
        CASE WHEN is_canceled = 'Y' THEN 6.0 ELSE imdb_score END
    ) AS effective_score
FROM netflix_titles;

---

PART 5 — MIN / MAX AS WINDOW FUNCTIONS

---

14. MIN / MAX across all rows — add to each row without collapsing

-- Show each title's score AND the overall best/worst score
SELECT
    title,
    genre,
    imdb_score,
    MIN(imdb_score) OVER () AS global_min,
    MAX(imdb_score) OVER () AS global_max,
    ROUND(imdb_score - AVG(imdb_score) OVER (), 2) AS vs_avg
FROM netflix_titles
ORDER BY imdb_score DESC;

---

15. MIN / MAX per group as a window — without GROUP BY collapse

-- Each title's score + the best and worst in its genre
SELECT
    title,
    genre,
    imdb_score,
    MIN(imdb_score) OVER (PARTITION BY genre) AS genre_min,
    MAX(imdb_score) OVER (PARTITION BY genre) AS genre_max,
    ROUND(
        imdb_score - AVG(imdb_score) OVER (PARTITION BY genre),
        2
    ) AS vs_genre_avg
FROM netflix_titles
ORDER BY genre, imdb_score DESC;

Result:

title	genre	imdb_score	genre_min	genre_max	vs_genre_avg
Squid Game	Drama	8.0	5.1	9.3	+0.43
Ozark	Drama	8.4	5.1	9.3	+0.83
Emily in Paris	Drama	6.3	5.1	9.3	-1.27

---

PART 6 — COMBINING ALL PATTERNS

---

16. Full Genre Report

WITH genre_stats AS (
    SELECT
        genre,
        COUNT(*)                         AS total_titles,
        MIN(imdb_score)                  AS min_score,
        MAX(imdb_score)                  AS max_score,
        ROUND(AVG(imdb_score), 2)        AS avg_score,
        MAX(imdb_score)
            - MIN(imdb_score)            AS score_range,
        MIN(release_year)                AS first_year,
        MAX(release_year)                AS latest_year,
        MAX(CASE WHEN is_original = 'Y'
            THEN imdb_score END)         AS best_original_score
    FROM netflix_titles
    WHERE is_canceled = 'N'
    GROUP BY genre
)
SELECT *
FROM genre_stats
WHERE total_titles >= 10
ORDER BY avg_score DESC;

---

🧠 Quick Reference

Goal	Pattern
Lowest value in column	MIN(col)
Highest value in column	MAX(col)
Range (spread)	MAX(col) - MIN(col)
MIN / MAX per group	GROUP BY genre + MIN(col)
Filter groups by MIN/MAX	HAVING MAX(col) > value
Full row with max value	WHERE col = (SELECT MAX(col) FROM ...)
One row with max (no ties)	ORDER BY col DESC LIMIT 1
Best per group	Correlated subquery or ROW_NUMBER() CTE
Conditional MIN / MAX	MIN(CASE WHEN ... THEN col END)
Add global min/max to rows	MIN(col) OVER () window function
Add group min/max to rows	MIN(col) OVER (PARTITION BY genre)

---

🧠 Mental Model

MIN / MAX — three levels of use:

1. AGGREGATE (with GROUP BY or whole table)
   → collapses rows into one result per group
   SELECT genre, MIN(score), MAX(score)
   FROM netflix_titles GROUP BY genre

2. SUBQUERY (to get the row behind the value)
   → find the full record that holds the min/max
   WHERE score = (SELECT MAX(score) FROM netflix_titles)

3. WINDOW FUNCTION (add to every row without collapsing)
   → each row keeps its own data + gets the group min/max added
   MIN(score) OVER (PARTITION BY genre)

Choosing between subquery vs LIMIT 1:
  Ties possible and all should appear  → subquery
  Just need any single top result      → ORDER BY ... LIMIT 1

MIN / MAX ignore NULLs automatically — same as SUM and AVG.
On text columns they use alphabetical order.
On date strings stored as 'YYYY-MM-DD' they work correctly as dates.