I asked six friends to rate six popular 2025 movies on a 1–5 scale, stored their answers in a normalized SQLite database, and loaded the data into R. The result is a small user–item ratings matrix, the same shape of data a recommender system starts from.
Two questions shape the analysis. First, how should ratings be handled when someone hasn’t seen a movie? Second, does it help to standardize ratings, so that generous and harsh raters are comparable?
I chose six widely released 2025 films across different genres, so that the survey was not tilted toward one kind of viewer.
dbGetQuery(con, "SELECT title, release_year, genre FROM movies ORDER BY movie_id")| title | release_year | genre |
|---|---|---|
| Sinners | 2025 | Horror |
| Superman | 2025 | Superhero |
| F1 | 2025 | Sports drama |
| Weapons | 2025 | Horror |
| KPop Demon Hunters | 2025 | Animated musical |
| Wicked: For Good | 2025 | Musical |
Each friend received the list of six movies and was asked to rate the ones they had seen with a whole number from 1 (poor) to 5 (excellent), leaving any unseen movie blank. The answers are in data/survey_responses.csv, one row per person and one column per movie. Respondents are labeled Friend 1 to Friend 6 rather than by name, because this repository is public.
As it turned out, all six friends had seen all six movies, so the collected data has no missing ratings. Section 6 explains how missing ratings are handled anyway, and tests that strategy by hiding some ratings on purpose.
The data is split into three tables instead of one wide table with a column per movie. People and movies each have their own table, and ratings is a junction table linking them. This is the standard way to model a many-to-many relationship: one person rates many movies, and one movie is rated by many people.
erDiagram
USERS ||--o{ RATINGS : gives
MOVIES ||--o{ RATINGS : receives
USERS {
int user_id PK
text name
}
MOVIES {
int movie_id PK
text title
int release_year
text genre
}
RATINGS {
int user_id PK, FK
int movie_id PK, FK
int rating
}
Three design choices matter:
(user_id, movie_id): a person cannot rate the same movie twice.CHECK (rating BETWEEN 1 AND 5): the database itself rejects an out-of-range value such as a mistyped 44.An unseen movie gets no row in ratings. It is never stored as 0, because a zero would look like a very bad rating and would drag the movie’s average down.
-- 01_create_tables.sql
-- Normalized schema for a small user-item ratings dataset (SQLite).
--
-- users 1 ──< ratings >── 1 movies
--
-- A person can rate many movies and a movie can be rated by many people,
-- so the many-to-many relationship lives in the `ratings` junction table.
-- A movie someone has NOT seen is simply absent from `ratings`; it is never
-- stored as a 0, because 0 would look like a (terrible) rating.
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS ratings;
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE -- first name or pseudonym only
);
CREATE TABLE movies (
movie_id INTEGER PRIMARY KEY,
title TEXT NOT NULL UNIQUE,
release_year INTEGER NOT NULL,
genre TEXT NOT NULL
);
CREATE TABLE ratings (
user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
movie_id INTEGER NOT NULL REFERENCES movies(movie_id) ON DELETE CASCADE,
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
PRIMARY KEY (user_id, movie_id) -- one rating per person per movie
);The movies are inserted by hand:
-- 02_insert_movies.sql
-- The six movies in the survey. Titles must match the column headers in
-- data/survey_responses.csv exactly.
INSERT INTO movies (movie_id, title, release_year, genre) VALUES
(1, 'Sinners', 2025, 'Horror'),
(2, 'Superman', 2025, 'Superhero'),
(3, 'F1', 2025, 'Sports drama'),
(4, 'Weapons', 2025, 'Horror'),
(5, 'KPop Demon Hunters', 2025, 'Animated musical'),
(6, 'Wicked: For Good', 2025, 'Musical');The people and their ratings are inserted by sql/03_insert_users_and_ratings.sql. That file is generated from the survey CSV by R/build_database.R, which first checks that every rating is a whole number from 1 to 5. The first lines of the file:
-- 03_insert_users_and_ratings.sql
-- GENERATED by R/build_database.R from data/survey_responses.csv.
-- Do not edit by hand; edit the CSV and re-run the script.
INSERT INTO users (user_id, name) VALUES
(1, 'Friend 1'),
(2, 'Friend 2'),
(3, 'Friend 3'),
(4, 'Friend 4'),
(5, 'Friend 5'),
(6, 'Friend 6');
INSERT INTO ratings (user_id, movie_id, rating) VALUES
(1, 1, 5),
(1, 2, 5),
(1, 3, 5),To rebuild the database from scratch:
Rscript R/build_database.RSQLite stores the database in a local file, so there is no server, username or password anywhere in this project. If the data lived in PostgreSQL instead, the connection would read its credentials from environment variables in a private ~/.Renviron file that is never committed:
con <- DBI::dbConnect(
RPostgres::Postgres(),
host = Sys.getenv("PGHOST"),
dbname = Sys.getenv("PGDATABASE"),
user = Sys.getenv("PGUSER"),
password = Sys.getenv("PGPASSWORD")
)This query returns every possible person–movie pair. CROSS JOIN creates all combinations, and LEFT JOIN attaches a rating where one exists. A pair with no rating comes back as SQL NULL, which R reads as NA. Missing ratings therefore show up as visible NA rows instead of silently not existing.
SELECT u.user_id,
u.name AS person,
m.movie_id,
m.title AS movie,
m.genre,
r.rating
FROM users u
CROSS JOIN movies m
LEFT JOIN ratings r
ON r.user_id = u.user_id
AND r.movie_id = m.movie_id
ORDER BY u.user_id, m.movie_idratings <- as_tibble(ratings) |>
mutate(movie = factor(movie, levels = unique(movie)))
glimpse(ratings)Rows: 36
Columns: 6
$ user_id <int> 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 4, 4, 4…
$ person <chr> "Friend 1", "Friend 1", "Friend 1", "Friend 1", "Friend 1", "…
$ movie_id <int> 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3…
$ movie <fct> Sinners, Superman, F1, Weapons, KPop Demon Hunters, Wicked: F…
$ genre <chr> "Horror", "Superhero", "Sports drama", "Horror", "Animated mu…
$ rating <int> 5, 5, 5, 3, 4, 5, 5, 5, 5, 3, 4, 5, 5, 5, 5, 3, 4, 5, 5, 5, 5…n_people <- n_distinct(ratings$person)
n_movies <- n_distinct(ratings$movie)
n_observed <- sum(!is.na(ratings$rating))
n_missing <- sum(is.na(ratings$rating))
n_in_db <- dbGetQuery(con, "SELECT COUNT(*) AS n FROM ratings")$n
tibble(
people = n_people,
movies = n_movies,
possible_pairs = nrow(ratings),
ratings_observed = n_observed,
ratings_missing = n_missing,
matches_database = n_observed == n_in_db
)| people | movies | possible_pairs | ratings_observed | ratings_missing | matches_database |
|---|---|---|---|---|---|
| 6 | 6 | 36 | 36 | 0 | TRUE |
All 36 person–movie pairs are present. 36 have a rating and 0 are missing. The observed count matches the ratings table exactly, so the joins neither lost nor duplicated anything.
Pivoting to one row per person and one column per movie gives the matrix a recommender system would use.
user_item <- ratings |>
select(person, movie, rating) |>
pivot_wider(names_from = movie, values_from = rating)
user_item| person | Sinners | Superman | F1 | Weapons | KPop Demon Hunters | Wicked: For Good |
|---|---|---|---|---|---|---|
| Friend 1 | 5 | 5 | 5 | 3 | 4 | 5 |
| Friend 2 | 5 | 5 | 5 | 3 | 4 | 5 |
| Friend 3 | 5 | 5 | 5 | 3 | 4 | 5 |
| Friend 4 | 5 | 5 | 5 | 3 | 4 | 5 |
| Friend 5 | 4 | 5 | 5 | 3 | 5 | 5 |
| Friend 6 | 5 | 4 | 5 | 3 | 5 | 5 |
ggplot(ratings, aes(x = movie, y = fct_rev(person), fill = rating)) +
geom_tile(color = "white", linewidth = 1) +
geom_text(aes(label = rating), na.rm = TRUE, size = 4) +
scale_fill_gradient(low = "#fde0c5", high = "#c0392b", limits = c(1, 5),
na.value = "grey90", name = "Rating") +
labs(x = NULL, y = NULL) +
theme_minimal(base_size = 12) +
theme(panel.grid = element_blank(),
axis.text.x = element_text(angle = 25, hjust = 1))
n_patterns <- user_item |> select(-person) |> distinct() |> nrow()
largest_group <- user_item |> count(pick(-person)) |> pull(n) |> max()
unanimous <- ratings |> group_by(movie) |> filter(n_distinct(rating) == 1) |>
distinct(movie, rating)The friends agree a lot. Their 6 responses contain only 3 distinct patterns, and 4 people gave exactly the same six ratings. Every friend gave the same score to F1 (5), Weapons (3), Wicked: For Good (5). This is a very homogeneous group, which limits what six responses can reveal. That shows up in both analyses below.
A missing rating means “hasn’t seen it”. It does not mean “rated it 0”, and it does not mean “thinks it’s average”. The strategy follows from that:
ratings. The CROSS JOIN query turns the gap into an NA in R, so it can never be mistaken for a score.na.rm = TRUE), and always report how many ratings each average is based on.\[ \text{damped mean} = \frac{\sum \text{ratings} + k \cdot \bar{r}_{\text{all}}}{n + k} \]
Here \(n\) is the number of ratings for the movie, \(\bar{r}_{\text{all}}\) is the average of all ratings, and I use \(k = 2\).
The pipeline was built with blanks in mind. R/build_database.R reads a blank survey cell as missing and simply does not insert a row for it.
My survey happens to have no gaps. That is actually useful, because it means the true average of every movie is known. I can hide some ratings at random, compute averages from what is left, and measure how far each method lands from the truth. The hidden ratings are a simulation, not survey data.
Here is one example of the matrix with about 30% of ratings hidden:
set.seed(607)
observed <- ratings |> filter(!is.na(rating)) |> mutate(rating = as.numeric(rating))
hide_ratings <- function(data, p_missing) {
data |> mutate(rating = if_else(runif(n()) < p_missing, NA_real_, rating))
}
example <- hide_ratings(observed, 0.3)
example |>
select(person, movie, rating) |>
pivot_wider(names_from = movie, values_from = rating) |>
mutate(across(-person, ~ if_else(is.na(.x), "·", as.character(.x))))| person | Sinners | Superman | F1 | Weapons | KPop Demon Hunters | Wicked: For Good |
|---|---|---|---|---|---|---|
| Friend 1 | · | 5 | 5 | · | 4 | · |
| Friend 2 | 5 | 5 | · | · | · | 5 |
| Friend 3 | · | 5 | 5 | 3 | 4 | · |
| Friend 4 | · | 5 | · | 3 | · | 5 |
| Friend 5 | 4 | · | 5 | · | 5 | 5 |
| Friend 6 | 5 | 4 | 5 | · | 5 | 5 |
Three ways of averaging this incomplete matrix, compared with the real averages from the full survey:
k <- 2
average_three_ways <- function(data) {
overall <- mean(data$rating, na.rm = TRUE)
data |>
group_by(movie) |>
summarise(
n_left = sum(!is.na(rating)),
zero_fill = mean(replace_na(rating, 0)),
ignore_na = if (n_left > 0) mean(rating, na.rm = TRUE) else overall,
damped = (sum(rating, na.rm = TRUE) + k * overall) / (n_left + k),
.groups = "drop"
)
}
true_means <- observed |> group_by(movie) |> summarise(true_mean = mean(rating))
average_three_ways(example) |>
left_join(true_means, by = "movie") |>
relocate(true_mean, .after = n_left) |>
mutate(across(where(is.double), ~ round(.x, 2)))| movie | n_left | true_mean | zero_fill | ignore_na | damped |
|---|---|---|---|---|---|
| Sinners | 3 | 4.83 | 2.33 | 4.67 | 4.65 |
| Superman | 5 | 4.83 | 4.00 | 4.80 | 4.75 |
| F1 | 4 | 5.00 | 3.33 | 5.00 | 4.88 |
| Weapons | 2 | 3.00 | 1.00 | 3.00 | 3.82 |
| KPop Demon Hunters | 4 | 4.33 | 3.00 | 4.50 | 4.55 |
| Wicked: For Good | 4 | 5.00 | 3.33 | 5.00 | 4.88 |
Filling the gaps with zero is clearly wrong: every movie that lost a rating drops sharply, however well it was liked. One random draw can be lucky or unlucky, though, so the next step repeats the experiment many times.
simulate <- function(p_missing, reps = 500) {
map_dfr(seq_len(reps), \(i) {
hide_ratings(observed, p_missing) |>
average_three_ways() |>
mutate(rep = i)
}) |>
left_join(true_means, by = "movie") |>
pivot_longer(c(zero_fill, ignore_na, damped), names_to = "method", values_to = "estimate") |>
group_by(method) |>
summarise(mean_abs_error = mean(abs(estimate - true_mean)), .groups = "drop") |>
mutate(p_missing = p_missing)
}
set.seed(607)
sim_results <- map_dfr(c(0.1, 0.3, 0.5, 0.7), simulate) |>
mutate(method = recode(method,
zero_fill = "Fill gaps with 0",
ignore_na = "Ignore gaps (na.rm)",
damped = "Damped mean (k = 2)"))
sim_results |>
mutate(mean_abs_error = round(mean_abs_error, 3),
p_missing = paste0(p_missing * 100, "% hidden")) |>
pivot_wider(names_from = p_missing, values_from = mean_abs_error)| method | 10% hidden | 30% hidden | 50% hidden | 70% hidden |
|---|---|---|---|---|
| Damped mean (k = 2) | 0.156 | 0.196 | 0.259 | 0.345 |
| Ignore gaps (na.rm) | 0.020 | 0.052 | 0.099 | 0.180 |
| Fill gaps with 0 | 0.445 | 1.339 | 2.259 | 3.103 |
ggplot(sim_results, aes(x = p_missing, y = mean_abs_error, color = method)) +
geom_line(linewidth = 1) +
geom_point(size = 2.5) +
scale_x_continuous(labels = scales::percent, breaks = c(0.1, 0.3, 0.5, 0.7)) +
scale_color_manual(values = c("Fill gaps with 0" = "grey60",
"Ignore gaps (na.rm)" = "#2c7fb8",
"Damped mean (k = 2)" = "#c0392b"), name = NULL) +
labs(x = "Share of ratings hidden", y = "Mean absolute error (rating points)") +
theme_minimal(base_size = 12) +
theme(legend.position = "top")
With 30% of ratings hidden, filling the gaps with zero misses the true average by 1.34 points on average. Ignoring the gaps misses by 0.05, and the damped mean by 0.2. Zero-filling is several times worse than either alternative at every level, which settles the first question: gaps must never be treated as zeros.
The more interesting result is that simply ignoring the gaps beat the damped mean at every level, even with 70% of ratings hidden (0.18 vs 0.35). I expected the reverse, and the reason lies in this particular survey. The friends rated each movie almost identically, so even one remaining rating is already close to the true average. Damping then has no noise to smooth out. It only adds bias, pulling Weapons (a unanimous 3) up toward the overall 4.5 and F1 and Wicked (unanimous 5s) down.
The damped mean earns its place in the opposite situation, where people disagree about a movie and only one or two ratings remain. There a single outlier can decide the ranking, and a little pull toward the overall average is a good trade.
My strategy: keep gaps as NA, never fill them with zero, average only the real ratings, and show the number of ratings next to every average. Damping is a tool to reach for when ratings are both sparse and disagree. For this group of consistent raters it would make the averages worse.
One caveat the simulation cannot capture: in a real survey, gaps are not random. People choose what to watch, so a horror fan who skips a musical probably would have rated it lower than the musical’s fans did. Random hiding tests the averaging methods but not that selection effect.
overall_mean <- mean(ratings$rating, na.rm = TRUE)
movie_summary <- ratings |>
group_by(movie, genre) |>
summarise(
n_ratings = sum(!is.na(rating)),
raw_mean = mean(rating, na.rm = TRUE),
damped_mean = (sum(rating, na.rm = TRUE) + k * overall_mean) / (n_ratings + k),
.groups = "drop"
) |>
arrange(desc(raw_mean))
movie_summary |>
mutate(across(c(raw_mean, damped_mean), ~ round(.x, 2)))| movie | genre | n_ratings | raw_mean | damped_mean |
|---|---|---|---|---|
| F1 | Sports drama | 6 | 5.00 | 4.88 |
| Wicked: For Good | Musical | 6 | 5.00 | 4.88 |
| Sinners | Horror | 6 | 4.83 | 4.75 |
| Superman | Superhero | 6 | 4.83 | 4.75 |
| KPop Demon Hunters | Animated musical | 6 | 4.33 | 4.38 |
| Weapons | Horror | 6 | 3.00 | 3.38 |
movie_summary |>
mutate(movie = fct_reorder(movie, raw_mean)) |>
ggplot(aes(x = raw_mean, y = movie)) +
geom_vline(xintercept = overall_mean, linetype = "dashed", color = "grey50") +
geom_segment(aes(x = 1, xend = raw_mean, yend = movie), color = "grey80", linewidth = 1) +
geom_point(size = 3.5, color = "#c0392b") +
geom_text(aes(label = sprintf("%.2f", raw_mean)), nudge_x = 0.22, size = 3.6) +
scale_x_continuous(limits = c(1, 5.3), breaks = 1:5) +
labs(x = "Average rating (1–5)", y = NULL) +
theme_minimal(base_size = 12)
Across all 36 ratings the average is 4.5, so this group liked these movies overall. The highest-rated are F1 and Wicked: For Good, rated 5 by every friend, and the lowest is Weapons at 3. With every movie rated by all 6 friends, the damped mean pulls every average toward the overall mean by the same proportion, so it leaves the ranking unchanged.
People use rating scales differently. For one person “good” is a 3, and for another it is a 5. Mean-centering subtracts each person’s own average from their ratings, so +1 means “one point above what this person usually gives”. Z-scores also divide by the person’s standard deviation, which puts people who use the whole scale and people who stick to 4s and 5s on the same footing.
person_summary <- ratings |>
filter(!is.na(rating)) |>
group_by(person) |>
summarise(
movies_rated = n(),
avg_given = mean(rating),
sd_given = sd(rating),
.groups = "drop"
)
person_summary |>
mutate(across(c(avg_given, sd_given), ~ round(.x, 2)))| person | movies_rated | avg_given | sd_given |
|---|---|---|---|
| Friend 1 | 6 | 4.5 | 0.84 |
| Friend 2 | 6 | 4.5 | 0.84 |
| Friend 3 | 6 | 4.5 | 0.84 |
| Friend 4 | 6 | 4.5 | 0.84 |
| Friend 5 | 6 | 4.5 | 0.84 |
| Friend 6 | 6 | 4.5 | 0.84 |
standardized <- ratings |>
filter(!is.na(rating)) |>
group_by(person) |>
mutate(
person_sd = sd(rating),
centered = rating - mean(rating),
# sd is NA with one rating and 0 when every rating is identical;
# either way the person says nothing about relative preference
z_score = if_else(is.na(person_sd) | person_sd == 0, 0, centered / person_sd)
) |>
ungroup()
rank_comparison <- standardized |>
group_by(movie) |>
summarise(
raw_mean = mean(rating),
centered_mean = mean(centered),
z_mean = mean(z_score),
.groups = "drop"
) |>
mutate(
raw_rank = min_rank(desc(raw_mean)),
centered_rank = min_rank(desc(centered_mean)),
z_rank = min_rank(desc(z_mean))
) |>
arrange(raw_rank)
rank_comparison |>
mutate(across(where(is.double), ~ round(.x, 2)))| movie | raw_mean | centered_mean | z_mean | raw_rank | centered_rank | z_rank |
|---|---|---|---|---|---|---|
| F1 | 5.00 | 0.50 | 0.60 | 1 | 1 | 1 |
| Wicked: For Good | 5.00 | 0.50 | 0.60 | 1 | 1 | 1 |
| Sinners | 4.83 | 0.33 | 0.40 | 3 | 3 | 3 |
| Superman | 4.83 | 0.33 | 0.40 | 3 | 3 | 3 |
| KPop Demon Hunters | 4.33 | -0.17 | -0.20 | 5 | 5 | 5 |
| Weapons | 3.00 | -1.50 | -1.79 | 6 | 6 | 6 |
rank_changes_centered <- sum(rank_comparison$raw_rank != rank_comparison$centered_rank)
rank_changes_z <- sum(rank_comparison$raw_rank != rank_comparison$z_rank)
same_average <- n_distinct(round(person_summary$avg_given, 9)) == 1
same_spread <- n_distinct(round(person_summary$sd_given, 9)) == 1Mean-centering changed the rank of 0 of 6 movies, and z-scores changed 0.
The reason is visible in the table above: every friend’s average rating is exactly 4.5. Nobody in this group is a generous or a harsh rater relative to the others, so subtracting each person’s average subtracts the same number from everyone. The result is a shifted version of the raw ratings in the same order. Their spreads are identical too, so dividing by the standard deviation changes nothing either.
For this dataset, standardization is not beneficial. It is harmless but adds nothing. It would matter in a larger survey, and the conditions are specific:
R/build_database.R, with no retyping.