Five minutes with genre in movie-ratings

Someone asked what is in genre on movie-ratings, and the honest answer took one query.

9,000 rows, no nulls, 8 distinct values. Primary genre.

8 values, and they are not evenly spread: romance 21.7%, sci-fi 18.4%, comedy 14.8%, thriller 14.0%. The largest takes 21.7% on its own.

Read the distinct list rather than the distinct count. Casing differences and trailing whitespace produce values that look identical in a report and group separately in SQL, and the count will not show you that — select distinct genre order by 1 will, in about a second.

select
  count(*)                as rows,
  count(genre)            as present,
  count(*) - count(genre) as nulls,
  count(distinct genre)   as distinct_values
from movie_ratings;

8 values is past the point where a bar chart stays readable. Group the tail explicitly rather than letting a chart library decide which 0 categories to drop for you.

Where this bites: a group-by on this column produces 8 rows today. If it is a column an upstream system can add values to, it produces an unknown number tomorrow, and any dashboard laid out for 8 categories reflows without warning. Values arriving is a schema change that no schema check catches.

The grain is one row per user-film rating, which is the context every one of those numbers depends on. None of them survive a change of grain, which is why "profile the column" and "profile the table" are the same job. Full schema, and the CSV, on the dataset page.