To get output of movie with its genre

select  *
from    Movie m
join    Genre g
on      g.movieId = m.movieId
join    (
        select  r.mid
        ,       sum(Rating) as SumRating
        from    Rating r
        group by
                r.mid
        ) r
on      r.mid = m.movieId
join    (
        select  g.id as gid
        ,       max(SumRating) as MaxGenreRating
        from    (
                select  r.mid
                ,       sum(Rating) as SumRating
                from    Rating r
                group by
                        r.mid
                ) r
        join    Genre g
        on      g.movieId = r.mid
        group by
                g.id
        ) filter
on      filter.gid = g.id
        and filter.MaxGenreRating = r.SumRating

select movie_id, count(*) from genres_movies group by movie_id

select G.name, Round(avg(numgenres),2) as MuddleFactor
from genres_movies GM

inner join
(select movie_id, count(*) as numGenres
 from genres_movies
 group by movie_id) MNG 
 on MNG.movie_id = GM.movie_id

inner join genres G
on GM.genre_id = G.id

group by G.name
order by MuddleFactor desc

// Define a model for linear regression.
const model = tf.sequential();
model.add(tf.layers.dense({ units: 1, inputDim: 3 }));

// Prepare the model for training: Specify the loss and the optimizer.
model.compile({ loss: 'meanSquaredError', optimizer: 'sgd' });
// Generate some synthetic data for training.

//[action, adventure, romance]
const xs = tf.tensor2d([[1, 1, 0], [1, 0, 1]]);
//target data should be rating from 1 to 5
const ys = tf.tensor2d([[3], [2]]);

// Train the model using the data.
model.fit(xs, ys).then(() => {
  // Use the model to do inference on a data point the model hasn't seen before:
  // Open the browser devtools to see the output
  model.predict(tf.tensor2d([[1, 0, 0]])).print();
});

Tensor
     [[1.6977279],]

 rdd = sc.parallelize([('librarian', (1, [0, 0, 1, 0])), ('librarian', (1, [0, 1, 0, 0])),\
                      ('librarian', (1, [0, 1, 0, 0]))])

result = rdd.reduceByKey(lambda x, y: ((x[0]+y[0]),\
                            (x[1][0]+y[1][0]), (x[1][1]+y[1][1]), (x[1][2]+y[1][2]), (x[1][3]+y[1][3]) ))

 rdd = sc.parallelize([('librarian', (1, [0, 0, 1, 0])), ('librarian', (1, [0, 1, 0, 0])),\
                      ('librarian', (1, [0, 1, 0, 0]))])

result = rdd.reduceByKey(lambda x, y: ( x[0] + y[0],\
                            [x[1][0]+y[1][0], x[1][1]+y[1][1], x[1][2]+y[1][2], x[1][3]+y[1][3] ] ))
print (result.collect())

SELECT 
 mo.title title
 , GROUP_CONCAT(DISTINCT p.people) people
  , GROUP_CONCAT(DISTINCT ge.genre) genre
FROM 
  Movie mo
  inner join  movie_people mp 
    on mo.movie_id = mp.movie_id
  inner join people p 
    on mp.people_id = p.people_id
  inner join movie_genre mg
    on mg.movie_id = mp.movie_id
  inner join genre ge
    on ge.genre_id = mg.genre_id
 GrOUP By mo.movie_id,mo.title;

CREATE TABLE movie_people
    (`movie_id` int, `people_id` int)
;

INSERT INTO movie_people
    (`movie_id`, `people_id`)
VALUES
    (1, 1),
    (1, 2),
    (2, 1),
    (2, 2)

;


CREATE TABLE `people`
    (`people_id` int, `people` varchar(14))
;

INSERT INTO `people`
    (`people_id`, `people`)
VALUES
    (1, 'Dwayne Johnson'),
    (2, 'Jason Douglas')
;


CREATE TABLE `movie_genre`
    (`movie_id` int, `genre_id` int)
;

INSERT INTO `movie_genre`
    (`movie_id`, `genre_id`)
VALUES
    (1, 1),
    (1, 3),
    (1, 4),
    (2, 1),
    (2, 2),
    (2, 4)
;


CREATE TABLE `genre`
    (`genre_id` int, `genre` varchar(9))
;

INSERT INTO `genre`
    (`genre_id`, `genre`)
VALUES
    (1, 'Action'),
    (2, 'Animation'),
    (3, 'Drama'),
    (4, 'Crime')
;


CREATE TABLE Movie
    (`movie_id` int, `title` varchar(6))
;

INSERT INTO Movie
    (`movie_id`, `title`)
VALUES
    (1, 'snitch'),
    (2, 'snitch')
;

title   people                          genre
snitch  Jason Douglas,Dwayne Johnson    Action,Crime,Drama
snitch  Dwayne Johnson,Jason Douglas    Animation,Crime,Action