4

Disclaimer

I do NOT want any explicit queries to my question. I want suggestions regarding how I can best approach this problem. I am eager to learn SQL by experimentation, but for my sake, I'd prefer not to have my hand held. Thanks!

Database/ER model

I am using the DVD Rental database found on the PostgreSQLTutorial website. Here's the ER diagram they've provided (the zip is provided on the site):

enter image description here

Preliminary problem leading up to question

For my own practice, I wanted to see if I could come up with a list of customers (customer) and the genres (film_category) from which they've rented (payment and rental) movies, as well as counts for each of those genres per customer. Here is the query I wrote, which works correctly:

SELECT customer.first_name || ' ' || customer.last_name AS customer, category.name AS movie_genre, COUNT(category.name) AS num_rented

FROM customer JOIN payment ON customer.customer_id = payment.customer_id
JOIN rental ON payment.rental_id = rental.rental_id
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film_category ON inventory.film_id = film_category.film_id
JOIN category ON film_category.category_id = category.category_id

GROUP BY customer, movie_genre
ORDER BY customer, num_rented DESC;

Problem

To challenge myself further, I want to see if I can list each customer's name along with the names of movies they have not yet rented that belong to the genre from which they've purchased the most movies (or the first one, if there's a tie). For example, here's a snapshot of some of the data I get when running the above query:

enter image description here

And here's a preview of the first 19 rows of the film table (not joined in the above query, but it will need to be joined for this problem):

enter image description here

So for example, we see that Aaron Selby really likes Travel movies, so we want to list her name alongside all movies belonging to the Travel category, but only those movies she has not yet rented.

Question

Could someone please offer some guidance/hints as to where I can begin? I know I'll need to join the film table, but here are some challenges:

  • limiting the output for each customer to only the most common genre they've rented

  • based on that information, retrieving all movies from films corresponding to that genre that the customer has not yet rented

  • Check out the HAVING clause with the MAX aggregate function. – Jacob H Jul 2 '18 at 16:04
  • Yeah, I was considering how I could use HAVING and MAX. I'm guessing I'll need a subquery of sorts? Unfortunately (this is what I was hoping to do), I can't write MAX(COUNT(category.name)) because you apparently can't nest aggregates. – AleksandrH Jul 2 '18 at 16:08
  • At this point you need to consider something very important before moving forward. From my experience in retail, you not only need the list of movies the customer has not yet rented, you need the top rented movies in those customer's favorite categories, not rented yet by the customer. The only thing you need is to NAME the aggregation query to cross with other results: select A.a_field, B.b_field from ([agreggation query]) A [inner/left/whatever] join ([agreggation query]) B on A.field_x = B.field_y WHERE [whatever] order by [whaterever] – AMG Jul 2 '18 at 16:46
  • @AMG Great point, yeah. So if I'm really feeling masochistic, I could probably find the average rating for the customer's rented movies and then ensure that whatever I recommend is at least above that rating so I don't recommend them things they might not like. Good point about naming the subquery. Thanks! – AleksandrH Jul 2 '18 at 17:50
0

Most common ...

In statistics, this is called mode.

Yes, PostgreSQL has an aggregate called mode. I haven't messed with it in PostgreSQL.

Retrieves those not wanted...

You want the resulting set of all movies for a genre minus the data set containing all movies rented by a customer for that genre

In PostgreSQL, you'll want to say EXCEPT instead of MINUS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.