This report collects ratings for six titles across five raters,
stores them in a small relational SQL database (users,
movies, ratings), and loads that data into R
for analysis. The goal is to practice the full round trip — SQL schema
design, a many-to-many ratings table, and reading SQL output into a tidy
R data frame — while handling the reality that not every rater has an
opinion on every title. Full SQL source: create_tables.sql.
Data source disclosure: for this assignment I used a small representative sample derived from publicly available aggregate ratings on Goodreads and IMDb, rather than a live survey of five people I personally asked. IMDb reports Grey’s Anatomy at 7.6/10 and Titanic at 8.0/10, which convert to approximately 3.8/5 and 4.0/5; Goodreads reports Perfume at 4.04/5 and The Little Prince at 4.34/5. The five rows in
ratingswere constructed so each title’s average matches its public score, with a couple of cells left missing (NULL) to model a rater who hasn’t seen or read that title. The rows do not represent people I personally surveyed; ratings are on a 1–5 scale, and unavailable ratings are stored as missing values rather than as zero.
The database is built at run time from a .sql
file hosted on GitHub, so the whole pipeline is reproducible from a URL
rather than a local file: R downloads the raw SQL text, then executes
each statement against a fresh, in-memory SQLite database.
sql_url <- "https://raw.githubusercontent.com/NawelMe/DATA607-Fall-2026/main/week-02/week2a_movie_ratings/create_tables.sql"
sql_lines <- readLines(sql_url, warn = FALSE)
# Drop full-line comments BEFORE splitting into statements. Doing this after
# splitting would fail: this file opens with a multi-line comment block, so
# the first "chunk" between semicolons starts with a comment line even
# though it ends with a real CREATE TABLE statement — checking only whether
# the whole chunk starts with "--" would wrongly discard it.
sql_text <- sql_lines |>
discard(~ str_starts(str_trim(.x), "--")) |>
paste(collapse = "\n")
statements <- str_split(sql_text, ";")[[1]] |>
str_trim() |>
discard(~ .x == "")
con <- dbConnect(RSQLite::SQLite(), ":memory:")
walk(statements, ~ dbExecute(con, .x))A JOIN across all three tables turns the normalized SQL
tables back into one wide, human-readable table — this is the “user-item
matrix” the assignment mentions.
ratings_df <- dbGetQuery(con, "
SELECT u.name AS user_name,
m.title AS movie_title,
r.rating
FROM ratings r
JOIN users u ON u.user_id = r.user_id
JOIN movies m ON m.movie_id = r.movie_id
")
ratings_df <- ratings_df |> as_tibble()
head(ratings_df, 10)## # A tibble: 10 × 3
## user_name movie_title rating
## <chr> <chr> <int>
## 1 Sample 1 Lolita 5
## 2 Sample 1 The Alchemist 5
## 3 Sample 1 Perfume 5
## 4 Sample 1 Grey's Anatomy 5
## 5 Sample 1 The Little Prince 5
## 6 Sample 1 Titanic 5
## 7 Sample 2 Lolita 4
## 8 Sample 2 The Alchemist 4
## 9 Sample 2 Perfume 4
## 10 Sample 2 Grey's Anatomy 4Not every person has seen every movie, so rating can be
NA. We keep those rows (they’re informative — “hasn’t seen
it” is different from “hated it”) but exclude them from any average.
ratings_df |>
summarize(
total_pairs = n(),
missing_count = sum(is.na(rating)),
missing_pct = round(100 * mean(is.na(rating)), 1)
)## # A tibble: 1 × 3
## total_pairs missing_count missing_pct
## <int> <int> <dbl>
## 1 30 2 6.7Average rating per movie (missing ratings excluded
via na.rm = TRUE):
ratings_df |>
group_by(movie_title) |>
summarize(
avg_rating = round(mean(rating, na.rm = TRUE), 2),
n_ratings = sum(!is.na(rating))
) |>
arrange(desc(avg_rating))## # A tibble: 6 × 3
## movie_title avg_rating n_ratings
## <chr> <dbl> <int>
## 1 The Little Prince 4.4 5
## 2 Perfume 4 5
## 3 The Alchemist 4 4
## 4 Titanic 4 4
## 5 Grey's Anatomy 3.8 5
## 6 Lolita 3.8 5Average rating given per user — a quick check for whether someone rates systematically higher or lower than the group:
ratings_df |>
group_by(user_name) |>
summarize(
avg_given = round(mean(rating, na.rm = TRUE), 2),
n_seen = sum(!is.na(rating))
) |>
arrange(desc(avg_given))## # A tibble: 5 × 3
## user_name avg_given n_seen
## <chr> <dbl> <int>
## 1 Sample 1 5 6
## 2 Sample 2 4.17 6
## 3 Sample 3 4.17 6
## 4 Sample 4 3.4 5
## 5 Sample 5 3 5The Little Prince comes out on top (4.4) and
Lolita/Grey’s Anatomy tie for lowest (3.8), which
simply reproduces the public scores each row was built from — with only
five rows and two missing cells, this dataset is too small to draw any
real conclusion about rater behavior beyond confirming the pipeline
(schema, JOIN, missing-value handling) works correctly end to end.
Because the ratings are a constructed sample rather than five
independently surveyed people, they shouldn’t be read as evidence of
genuine disagreement between raters — that’s the main limitation to flag
if this were extended. To extend this work: (1) replace these
constructed rows with a live survey of five real people, which would
make patterns like “who rates highest on average” actually meaningful,
(2) add a genre column to movies to check
whether average rating varies by genre, and (3) reshape
ratings_df into a wide user-by-movie matrix
(pivot_wider) as a first step toward a recommender system,
since that matrix is exactly what collaborative filtering needs as
input.
Anthropic. (2026). Claude (model: claude-sonnet-5) [Large language model]. https://claude.ai/
I used Claude to help me plan the three-table SQL structure, troubleshoot the R code used to read and execute the SQL file, and improve parts of the written explanation. I adapted the code for my assignment, ran it in RStudio, checked the tables and averages, and revised the report based on the results I obtained.