SELECT g.genre_name, FLOOR(t.start_year / 10) * 10 AS decade, COUNT(*) AS movie_count FROM titles t JOIN title_genres tg ON t.title_id = tg.title_id JOIN genres g ON tg.genre_id = g.genre_id WHERE t.title_type = 'movie' AND t.start_year IS NOT NULL GROUP BY g.genre_name, decade ORDER BY decade, movie_count DESC;
-- 1. Create Genres Lookup Table CREATE TABLE genres ( genre_id SERIAL PRIMARY KEY, genre_name VARCHAR(50) UNIQUE NOT NULL ); imdb database
For the average user, it provides "metadata" that was once difficult to find: SELECT g
SELECT p.full_name AS actor_name, COUNT(*) AS collaborations FROM principals pr_director JOIN principals pr_actor ON pr_director.title_id = pr_actor.title_id JOIN people p ON pr_actor.person_id = p.person_id WHERE pr_director.category = 'director' AND pr_director.person_id = 'nm0000206' -- Replace with actual Director ID (e.g., Christopher Nolan) AND pr_actor.category = 'actor' GROUP BY p.full_name ORDER BY collaborations DESC LIMIT 5; Create People Table CREATE TABLE people ( person_id
– Alternative titles, including original title, international titles, and transliterated versions.
-- 3. Create People Table CREATE TABLE people ( person_id VARCHAR(20) PRIMARY KEY, -- e.g., 'nm0000206' full_name VARCHAR(200) NOT NULL, birth_year INTEGER, death_year INTEGER, primary_profession VARCHAR(200) );