SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Movie (
    id integer primary key,
    title text not null,
    year integer
);

CREATE TABLE Genre (
    movie_id integer references Movie(id),
    genre text not null
);

CREATE TABLE Keyword (
    movie_id integer references Movie(id),
    keyword text not null
);

INSERT INTO Movie VALUES (315, 'Harry Potter and the Deathly Hallows', 2011);
INSERT INTO Movie VALUES (407, 'Cinderella', 2015);
INSERT INTO Movie VALUES (826, 'The Shape of Water', 2017);
INSERT INTO Movie VALUES (799, 'Enchanted', 2007);
INSERT INTO Movie VALUES (523, 'How to Train Your Dragon', 2010);
INSERT INTO Movie VALUES (618, 'Crazy Rich Asians', 2018);

INSERT INTO Genre VALUES (315, 'adventure');
INSERT INTO Genre VALUES (315, 'fantasy');
INSERT INTO Genre VALUES (315, 'mystery');
INSERT INTO Genre VALUES (315, 'drama');

INSERT INTO Genre VALUES (407, 'drama');
INSERT INTO Genre VALUES (407, 'fantasy');

INSERT INTO Genre VALUES (826, 'drama');
INSERT INTO Genre VALUES (826, 'thriller');
INSERT INTO Genre VALUES (826, 'adventure');
INSERT INTO Genre VALUES (826, 'horror');

INSERT INTO Genre VALUES (799, 'fantasy');
INSERT INTO Genre VALUES (799, 'comedy');
INSERT INTO Genre VALUES (799, 'romance');

INSERT INTO Genre VALUES (523, 'drama');
INSERT INTO Genre VALUES (523, 'fantasy');

INSERT INTO Genre VALUES (618, 'romance');
INSERT INTO Genre VALUES (618, 'comedy');

INSERT INTO Keyword VALUES (315, 'magic');
INSERT INTO Keyword VALUES (315, 'wizards');
INSERT INTO Keyword VALUES (315, 'witch');
INSERT INTO Keyword VALUES (315, 'friendship');
INSERT INTO Keyword VALUES (315, 'abuse');

INSERT INTO Keyword VALUES (407, 'prince');
INSERT INTO Keyword VALUES (407, 'fairy tale');
INSERT INTO Keyword VALUES (407, 'magic');
INSERT INTO Keyword VALUES (407, 'poor girl');
INSERT INTO Keyword VALUES (407, 'abuse');

INSERT INTO Keyword VALUES (826, 'scientist');
INSERT INTO Keyword VALUES (826, 'mute');
INSERT INTO Keyword VALUES (826, 'friendship');

INSERT INTO Keyword VALUES (799, 'musical');
INSERT INTO Keyword VALUES (799, 'magic');
INSERT INTO Keyword VALUES (799, 'witch');
INSERT INTO Keyword VALUES (799, 'friendship');

INSERT INTO Keyword VALUES (523, 'viking');
INSERT INTO Keyword VALUES (523, 'boy');
INSERT INTO Keyword VALUES (523, 'fire');

INSERT INTO Keyword VALUES (618, 'singapore');
INSERT INTO Keyword VALUES (618, 'wedding');
INSERT INTO Keyword VALUES (618, 'money');
46 rows affected

DECLARE @tmpMovieID INT;
SET @tmpMovieID = (SELECT DISTINCT id
                   FROM   movie
                   WHERE  Cast(title AS NVARCHAR(MAX)) = 'Harry Potter and the Deathly Hallows');
                   
SELECT id,
       Cast(title AS NVARCHAR(max))          AS title,
       Count(Cast(g.genre AS NVARCHAR(max))) AS genre
FROM   movie m
       INNER JOIN genre g ON m.id = g.movie_id
WHERE  Cast(g.genre AS NVARCHAR(max)) IN (SELECT DISTINCT Cast(genre AS NVARCHAR(max))
                                          FROM   genre
                                          WHERE  movie_id = @tmpMovieID)
GROUP  BY id, Cast(title AS NVARCHAR(max));  


id
title
genre


315
Harry Potter and the Deathly Hallows
4

407
Cinderella
2

523
How to Train Your Dragon
2

799
Enchanted
1

826
The Shape of Water
2


DECLARE @tmpMovieID INT;
SET @tmpMovieID = (SELECT DISTINCT id
                   FROM   movie
                   WHERE  Cast(title AS NVARCHAR(MAX)) = 'Harry Potter and the Deathly Hallows');

SELECT     id,
           Cast(title AS NVARCHAR(max))            AS title,
           Count(Cast(k.keyword AS NVARCHAR(max))) AS keyword
FROM       movie m
           INNER JOIN keyword k ON m.id = k.movie_id
WHERE      Cast(k.keyword AS NVARCHAR(max)) IN (SELECT DISTINCT Cast(keyword AS NVARCHAR(max))
                                                FROM   keyword
                                                WHERE  movie_id = @tmpMovieID)
GROUP BY   id, cast(title AS NVARCHAR(max)); 


id
title
keyword


315
Harry Potter and the Deathly Hallows
5

407
Cinderella
2

799
Enchanted
3

826
The Shape of Water
1


DECLARE @tmpMovieID INT;
SET @tmpMovieID = (SELECT DISTINCT id
                   FROM   movie
                   WHERE  Cast(title AS NVARCHAR(MAX)) = 'Harry Potter and the Deathly Hallows');

SELECT tmpGenre.id                  AS id,
       tmpGenre.title               AS title,
       ISNULL(tmpGenre.genre, 0)    AS genre_freq,
       ISNULL(tmpKeyword.keyword,0) AS keyword_freq

FROM   (SELECT id,
               Cast(title AS NVARCHAR(MAX))          AS title,
               Count(Cast(g.genre AS NVARCHAR(MAX))) AS genre
        FROM   movie m
               INNER JOIN genre g ON m.id = g.movie_id
        WHERE  Cast(g.genre AS NVARCHAR(MAX)) IN (SELECT DISTINCT Cast(genre AS NVARCHAR(MAX))
                                                 FROM   genre
                                                 WHERE  movie_id = @tmpMovieID)
        GROUP  BY id, Cast(title AS NVARCHAR(MAX))) tmpGenre

       FULL OUTER JOIN (SELECT id,
                               Cast(title AS NVARCHAR(MAX))            AS title,
                               Count(Cast(k.keyword AS NVARCHAR(MAX))) AS Keyword
                        FROM   movie m
                               INNER JOIN keyword k ON m.id = k.movie_id
                        WHERE  Cast(k.keyword AS NVARCHAR(MAX)) IN
                               (SELECT DISTINCT Cast(keyword AS NVARCHAR(MAX))
                                FROM   keyword
                                WHERE  movie_id = @tmpMovieID)
                        GROUP  BY id, Cast(title AS NVARCHAR(MAX))) tmpKeyword

                    ON tmpGenre.id = tmpKeyword.id
WHERE  tmpGenre.id <> @tmpMovieID
ORDER  BY tmpGenre.genre DESC, tmpKeyword.keyword DESC;


id
title
genre_freq
keyword_freq


407
Cinderella
2
2

826
The Shape of Water
2
1

523
How to Train Your Dragon
2
0

799
Enchanted
1
3
id	title	genre
315	Harry Potter and the Deathly Hallows	4
407	Cinderella	2
523	How to Train Your Dragon	2
799	Enchanted	1
826	The Shape of Water	2