Problem with Postgresql query that requires that I can only use select once

Question

I am trying to get a solution to the following sql problem on codewars: Write an SQL query to retrieve the top 5 customers who have rented the most movies, excluding customers who have rented at least one movie with an 'NC-17' rating. Subqueries are forbidden to make the task a bit curiouser - you can use select only once

This is what I have tried:

select customer.customer_id,
first_name||' '||last_name as full_name,
count(*) as total_rentals 
from customer 
inner join rental on customer.customer_id = rental.customer_id
inner join  inventory on rental.inventory_id = inventory.inventory_id
right join film on inventory.film_id = film.film_id 
where film.rating !='NC-17' 
group by customer.customer_id,first_name||' '||last_name
order by count(*)desc,last_name 
limit 5

The problem is that the above excludes only the rows where the film rating is NC-17. I need to exclude whoever has watched a movie with a NC-17 rating.I am limited as the challenge only allows me to use select once so i cant use subqueries

Answer 1

You want to select customers and their movie counts. But you must exclude customers with certain movies. You cannot exclude the movies in the WHERE clause, because then you don't know any longer which customers rented such movies and which not. So, join the tables, aggregate the data as to get one row per customer, and then filter the aggregated results in the HAVING clause. At last sort that result and pick the top five.

Then, with those "top n" queries there is always the problem with ties. What if there are two or more customers with the same top #5 number of rented movies? You can decide then to pick 5 customers from the top 5 to n arbitrarily or select the tying customers along, thus maybe getting not only five, but six, seven or more sometimes. Use 5 ROWS ONLY or 5 ROWS WITH TIES accordingly.

select 
  c.customer_id,
  c.first_name || ' ' || c.last_name as full_name,
  count(*) as total_rentals 
from customer c
inner join rental r on r.customer_id = c.customer_id
inner join inventory i on i.inventory_id = r.inventory_id
inner join film f on f.film_id = i.film_id 
group by c.customer_id
having count(*) filter (where f.rating = 'NC-17') = 0
order by count(*) desc
fetch first 5 rows with ties;