-- film for actor_id 1
SELECT fa.actor_id, fc.category_id, fc.film_id 
FROM film_category AS fc INNER JOIN film_actor fa
    ON (fc.film_id = fa.film_id)
WHERE fa.actor_id = 1;

-- film count for actor_id 1
SELECT fa.actor_id, fc.category_id, 
    COUNT(fc.film_id) AS film_count
FROM film_category AS fc INNER JOIN film_actor fa
    ON (fc.film_id = fa.film_id)
WHERE fa.actor_id = 1
GROUP BY fa.actor_id, fc.category_id;

-- missing rows with film_count 0.
SELECT fa.actor_id, fc.category_id, 
    COUNT(fc.film_id) AS film_count
FROM film_category AS fc INNER JOIN film_actor fa
    ON (fc.film_id = fa.film_id)
WHERE fa.actor_id = 1
GROUP BY fa.actor_id, fc.category_id
ORDER BY fc.category_id;

SELECT fc.category_id, fa.actor_id, 
    COUNT(fc.film_id) AS film_count
FROM film_category AS fc INNER JOIN film_actor fa
    ON (fc.film_id = fa.film_id)
WHERE fa.actor_id = 1
GROUP BY fa.actor_id, fc.category_id
ORDER BY fc.category_id;

SELECT fc.category_id, fa.actor_id, 
    COUNT(fc.film_id) AS film_count
FROM film_category AS fc INNER JOIN film_actor fa
    ON (fc.film_id = fa.film_id)
GROUP BY fa.actor_id, fc.category_id
ORDER BY fa.actor_id, fc.category_id
LIMIT 30;

WITH fcount AS
(SELECT fc.category_id, fa.actor_id, 
    COUNT(fc.film_id) AS film_count
 FROM film_category AS fc INNER JOIN film_actor fa
    ON (fc.film_id = fa.film_id)
 GROUP BY fa.actor_id, fc.category_id
 ORDER BY fa.actor_id, fc.category_id)
SELECT category_id, actor_id, film_count
FROM fcount AS fc1
WHERE fc1.actor_id = 1;


WITH fcount AS
(SELECT fc.category_id, fa.actor_id, 
    COUNT(fc.film_id) AS film_count
 FROM film_category AS fc INNER JOIN film_actor fa
    ON (fc.film_id = fa.film_id)
 GROUP BY fa.actor_id, fc.category_id
 ORDER BY fa.actor_id, fc.category_id)
SELECT fc1.category_id, fc1. actor_id, fc1.film_count, fc2. actor_id, fc2.film_count
FROM fcount AS fc1 INNER JOIN fcount AS fc2
    ON (fc1.category_id = fc2.category_id)
WHERE fc1.actor_id = 1
AND fc2.actor_id = 2;

-- Length
WITH fcount AS
(SELECT fc.category_id, fa.actor_id, 
    COUNT(fc.film_id) AS film_count
 FROM film_category AS fc INNER JOIN film_actor fa
    ON (fc.film_id = fa.film_id)
 GROUP BY fa.actor_id, fc.category_id
 ORDER BY fa.actor_id, fc.category_id)
SELECT fc1.actor_id, 
    SQRT(SUM(film_count * film_count)) AS `vector length`
FROM fcount AS fc1
WHERE fc1.actor_id = 1
GROUP BY fc1.actor_id ;