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

[Procedurally transform subquery into join](https://stackoverflow.com/q/1772609/3404097) — philipxy, Apr 29 '23 at 07:46
On a side note: Right outer joins are something that most of us never use. They can make a query almost unreadable. Always use left outer joins instead. But why an outer join at all? You include thus films that nobody rented in your result. This doesn't seem to make sense. — Thorsten Kettner, Apr 29 '23 at 19:32

score 1 · Accepted Answer · answered Apr 29 '23 at 10:56

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;

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

1 Answers1