path: root/todo-lists/imdb/scratch.sql

-- which directors appear most often
SELECT director, COUNT(*)
FROM Movies
GROUP BY director
ORDER BY COUNT(*) DESC
LIMIT 10;

-- top-rated, most recent movies
SELECT *
FROM (
  SELECT *
  FROM Movies
  ORDER BY rating DESC
  LIMIT 20
)
ORDER BY YEAR DESC;

-- top-rated, most recent movies (ignore foreign)
SELECT *
FROM (
  SELECT *
  FROM Movies
  WHERE requiresSubtitles = 0
  ORDER BY rating DESC
  LIMIT 20
)
ORDER BY YEAR DESC;

-- most recent movies
SELECT *
FROM Movies
ORDER BY YEAR DESC
LIMIT 15;

-- most recent movies (ignore foreign)
SELECT *
FROM Movies
WHERE requiresSubtitles = 0
ORDER BY YEAR DESC
LIMIT 10;

-- only cartoons
SELECT *
FROM Movies
WHERE isCartoon = true;

-- only cartoons (ignore foreign)
SELECT *
FROM Movies
WHERE isCartoon = true AND requiresSubtitles = false;