Movie Watchlist and Ratings Tracker

Project roadmap

1. 01

   Design the tables

   ~1.5h

   Install PostgreSQL and pgAdmin. Write schema.sql with movies (title, year, director and runtime), genres (unique name), movie_genres (a junction table with a composite primary key), watchlist (movies you want to see, with the date added) and watch_log (movie, date watched, rating from 1 to 10 checked with a constraint, and a note). A movie can be watched more than once. Done when: the schema loads without errors.

2. 02

   Add sample data

   ~1h

   Write seed.sql with about 40 movies across 10 genres (most with 2 or 3 genres), a watchlist, and a watch history over the past year with ratings, including a rewatched movie. Done when: the data loads, and pgAdmin shows the rows.

3. 03

   Average rating by genre

   ~1h

   Write a query joining watch_log, movies, movie_genres and genres to show each genre's number of watches and average rating (rounded to 1 decimal), sorted by average rating. Think about whether a rewatch should count twice, and explain your choice in a comment. Done when: the query shows a sensible ranking of genres.

4. 04

   Movies watched per month

   ~1h

   Using date_trunc('month', ...) or to_char, count movies watched per month over the last 12 months, including months with none (using generate_series), and the average rating each month. Add a query for the favorite directors (at least 2 movies watched, sorted by average rating). Done when: every month appears, including empty ones.

5. 05

   Suggest movies

   ~1.5h

   Write a recommendation query: from your watchlist, rank movies by how much you like their genres (your average rating per genre, averaged over the movie's genres), and separately find genres you rate highly but have watched fewer than 3 times. Save the main queries as views. Done when: the recommendations favor genres you rate highly.

6. 06

   Test the queries

   ~1h

   Write test.sql that, inside a transaction, adds a few watches with known ratings, checks the genre averages and monthly counts change as expected (raising an error with a DO block otherwise), and confirms a rating of 11 is rejected, then rolls back. Done when: psql -f test.sql runs without errors.

Resources

• DocsPostgreSQL tutorial
• DocsPostgreSQL tutorial: joins between tables
• DocsPostgreSQL: date/time functions
• DocspgAdmin documentation
• DocsSQLBolt: interactive SQL lessons