CODEWITHSUNDEEP
mysqlsql
Chester Mew
Chester Mew

Reputation: 19

Write a query for each customer X, another customer Y who has rented at least one movie in common with X

Using the sakila database, write a query that finds, for each customer X, another customer Y who has rented at least one movie in common with X. Find all such pairs of Customers (X, Y) and against each pair, the number of overlapping movies. Order the results by the number of overlapping movies

I've tried using aliases, inner joins, and sub-queries. However, I believe there is a syntax error with my code.

SELECT o1.customer_id AS CustomerID1,
o2.customer_id AS CustomerID2,
COUNT(*) NoOfOverlappingMovies
FROM( ( (SELECT c.customer_id, f.film_id
        FROM customer AS c,
        JOIN rental AS r
        ON r.customer_id = c.customer_id)
        JOIN inventory AS i ON i.inventory_id = r.inventory_id)
        JOIN film AS f ON i.film_id = f.film_id
        ) AS o1
JOIN( ( (SELECT c.customer_id, f.film_id
        FROM customer AS c,
        JOIN rental AS r
        ON r.customer_id = c.customer_id)
        JOIN inventory AS i ON i.inventory_id = r.inventory_id)
        JOIN film AS f ON i.film_id = f.film_id
        ) AS o2
ON o2.film_id = o1.film_id AND o2.customer_id < o1.customer_id
GROUP BY o1.customer_id, o2.customer_id
ORDER BY COUNT(*) DESC;

The query should have 3 columns. CustomerID1, CustomerID2, and NoOfOverlappingMovies.

Upvotes: 0

Views: 1076

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269833

The query would seem to be:

select r1.customer_id, r2.customer_id,
       count(distinct r1.film_id) as num_films
from rental r1 join
     rental r2
     on r1.film_id = r2.film_id and
        r1.customer_id < r2.customer_id
group by r1.customer_id, r2.customer_id
order by num_films desc;

The other tables do not seem to be needed for this query.

Upvotes: 0

Roger Kreft
Roger Kreft

Reputation: 200

1) Do not use "," between "FROM" and "JOIN" parts.

2) Your parentheses are somewhat off. I tried to correct them as best i could without having the tables present:

SELECT o1.customer_id AS CustomerID1,
       o2.customer_id AS CustomerID2,
       COUNT(*) NoOfOverlappingMovies
FROM( (SELECT c.customer_id, f.film_id
        FROM customer AS c
        JOIN rental AS r ON r.customer_id = c.customer_id
        JOIN inventory AS i ON i.inventory_id = r.inventory_id
        JOIN film AS f ON i.film_id = f.film_id
        ) AS o1
        JOIN (SELECT c.customer_id, f.film_id
                FROM customer AS c
                JOIN rental AS r ON r.customer_id = c.customer_id
                JOIN inventory AS i ON i.inventory_id = r.inventory_id
                JOIN film AS f ON i.film_id = f.film_id
    ) AS o2 ON o2.film_id = o1.film_id AND o2.customer_id < o1.customer_id )
GROUP BY o1.customer_id, o2.customer_id
ORDER BY COUNT(*) DESC;

Upvotes: 0

Related Questions