/* Long 
WITH movie_genre AS (
  SELECT
    A.title,
    A.year,
    B.Genre AS genre
  FROM A
  JOIN B ON A.imdb_id = B.imdb_id
),
genre_totals AS (
  SELECT genre, COUNT(*) AS total
  FROM movie_genre
  WHERE genre IS NOT NULL AND genre != ''
  GROUP BY genre
  ORDER BY total DESC
  LIMIT 10
)
SELECT
  CAST(movie_genre.year AS CHAR) AS year,
  movie_genre.genre,
  COUNT(*) AS count
FROM movie_genre
JOIN genre_totals ON movie_genre.genre = genre_totals.genre
GROUP BY movie_genre.year, movie_genre.genre
ORDER BY movie_genre.year, movie_genre.genre;
*/
/* Wide */
WITH movie_genre AS (
  SELECT
    A.title,
    A.year,
    B.Genre AS genre
  FROM A
  JOIN B ON A.imdb_id = B.imdb_id
),
genre_totals AS (
  SELECT genre
  FROM movie_genre
  WHERE genre IS NOT NULL AND genre != ''
  GROUP BY genre
)
SELECT
  CAST(mg.year AS CHAR) AS year,
  COUNT(CASE WHEN mg.genre = 'Action' THEN 1 END) AS Action,
  COUNT(CASE WHEN mg.genre = 'Animation' THEN 1 END) AS Animation,
  COUNT(CASE WHEN mg.genre = 'Comedy' THEN 1 END) AS Comedy,
  COUNT(CASE WHEN mg.genre = 'Crime' THEN 1 END) AS Crime,
  COUNT(CASE WHEN mg.genre = 'Drama' THEN 1 END) AS Drama,
  COUNT(CASE WHEN mg.genre = 'Fantasy' THEN 1 END) AS Fantasy,
  COUNT(CASE WHEN mg.genre = 'Family' THEN 1 END) AS Family,
  COUNT(CASE WHEN mg.genre = 'History' THEN 1 END) AS History,
  COUNT(CASE WHEN mg.genre = 'Horror' THEN 1 END) AS Horror,
  COUNT(CASE WHEN mg.genre = 'Mystery' THEN 1 END) AS Mystery,
  COUNT(CASE WHEN mg.genre = 'Romance' THEN 1 END) AS Romance,
  COUNT(CASE WHEN mg.genre = 'Science Fiction' THEN 1 END) AS `Science Fiction`,
  COUNT(CASE WHEN mg.genre = 'Thriller' THEN 1 END) AS Thriller,
  COUNT(CASE WHEN mg.genre = 'War' THEN 1 END) AS War
FROM movie_genre mg
JOIN genre_totals gt ON mg.genre = gt.genre
GROUP BY mg.year
ORDER BY mg.year;