Hive Movie-Rating Analytics

USE moviedb;

CREATE TABLE ratings (
  userid      BIGINT,
  movieid     BIGINT,
  rating      DOUBLE,
  rating_time STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex'='^(.+?)::(.+?)::(.+?)::(.+?)$',
  'output.format.string' = '%1$s %2$s %3$s %4$s'
)
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/tmp/ratings.dat'
OVERWRITE INTO TABLE ratings;

WITH movie_ratings AS (
  SELECT movieid, AVG(rating) AS rating_avg
  FROM rating
  GROUP BY movieid
),
movie_with_year_genre AS (
  SELECT
    movieid,
    CAST(SUBSTR(title, -5, 4) AS INT) AS year,
    explode(split(genres, '\\|')) AS genre
  FROM movie
  WHERE title RLIKE '\\(\\d{4}\\)$'
),
ranked_genres AS (
  SELECT
    year,
    genre,
    AVG(rating_avg) AS genre_avg_rating,
    ROW_NUMBER() OVER (
      PARTITION BY year
      ORDER BY AVG(rating_avg) DESC, genre ASC
    ) AS rn
  FROM movie_with_year_genre
  JOIN movie_ratings USING (movieid)
  GROUP BY year, genre
)
SELECT year, genre, ROUND(genre_avg_rating, 3)
FROM ranked_genres
WHERE rn = 1
ORDER BY year;