CODEWITHSUNDEEP
sqloracle-database
Zelnorth
Zelnorth

Reputation: 23

Query to return users paired with their most viewed director in a movie rental DB

Assuming a DB with two tables structured like so:

Movies (Movie_ID, Director)

Rentals (CustomerID, Movie_ID)

I need to return users paired with the name of the director whose movies they have rented the most. I've figured out how to do it in MySQL but can't figure out the right syntax to get it to work in Oracle.

Working MySQL:

SELECT x.CustomerID, x.Director
  FROM (SELECT r.CustomerID, m.Director, COUNT(*) AS DirectorCount
          FROM Rentals AS r
          JOIN Movies AS m
            ON r.Movie_ID = m.Movie_ID
         GROUP BY r.CustomerID, m.Director
       ) x
  JOIN (SELECT x.CustomerID, MAX(x.DirectorCount) AS MaxDirectorCount
          FROM (SELECT r.CustomerID, m.Director, COUNT(*) AS DirectorCount
                  FROM Rentals AS r
                  JOIN Movies AS m
                    ON r.Movie_ID = m.Movie_ID
                 GROUP BY r.CustomerID, m.Director
               ) x
         GROUP BY x.CustomerID
       ) y
    ON x.CustomerID = y.CustomerID AND x.DirectorCount = y.MaxDirectorCount

Non Fuctioning Oracle:

WITH x AS (
    SELECT r.CustomerID, m.Director, COUNT(*) AS DirectorCount
          FROM Rentals AS r
          JOIN Movies AS m
            ON r.Movie_ID = m.Movie_ID
         GROUP BY r.CustomerID, m.Director
       )
WITH y AS (
                SELECT x.CustomerID, MAX(x.DirectorCount) AS MaxDirectorCount
          FROM (SELECT r.CustomerID, m.Director, COUNT(*) AS DirectorCount
                  FROM Rentals AS r
                  JOIN Movies AS m
                    ON r.Movie_ID = m.Movie_ID
                 GROUP BY r.CustomerID, m.Director
               )
         GROUP BY x.CustomerID
      ) 
SELECT x.CustomerID, x.Director
JOIN
ON x.CustomerID = y.CustomerID AND x.DirectorCount = y.MaxDirectorCount

Upvotes: 2

Views: 57

Answers (2)

Rene
Rene

Reputation: 10541

Table aliases in Oracle are specified without AS.

SELECT x.CustomerID, x.Director
  FROM (SELECT r.CustomerID, m.Director, COUNT(*) AS DirectorCount
          FROM Rentals r
          JOIN Movies  m
            ON r.Movie_ID = m.Movie_ID
         GROUP BY r.CustomerID, m.Director
       ) x
  JOIN (SELECT x.CustomerID, MAX(x.DirectorCount) AS MaxDirectorCount
          FROM (SELECT r.CustomerID, m.Director, COUNT(*) AS DirectorCount
                  FROM Rentals r
                  JOIN Movies m
                    ON r.Movie_ID = m.Movie_ID
                 GROUP BY r.CustomerID, m.Director
               ) x
         GROUP BY x.CustomerID
       ) y
    ON x.CustomerID = y.CustomerID AND x.DirectorCount = y.MaxDirectorCount

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35563

You can simplify this by using a window function to calculate the max count per customer, then filtering by that.

SELECT
       CustomerID
     , Director
     , directorcount
FROM (
    SELECT
          r.CustomerID
        , m.Director
        , COUNT(*) AS directorcount
        , max(COUNT(*)) over(partition by CustomerID) max_count
    FROM Rentals r
    JOIN Movies m ON r.Movie_ID = m.Movie_ID
    GROUP BY
          r.CustomerID
        , m.Director
    ) d
WHERE directorcount = max_count
  • Already mentioned your Oracle query failed because of "AS" when declaring table aliases.

Upvotes: 1

Related Questions