Postgresql - Using a variable in From in another select statement

Question

What I am trying to do is optimize my query by 95 percent as part of an assignment to learn psql. What I am trying to get is all the customers who have equal or greater than the person with the 3rd most borrowed books.

explain analyze
select clb.f_name, clb.l_name, noofbooks 
from 
      (select f_name, l_name, count(*) as noofbooks 
      from customer left join loaned_book on customer.customerid = loaned_book.customerid 
      group by f_name, l_name) as clb,
      (select f_name as fname, l_name as lname, count(*) as noofbooksf 
      from customer left join loaned_book on customer.customerid = loaned_book.customerid
      group by fname, lname order by noofbooksf  desc  LIMIT 1 OFFSET 2) as clb1
where noofbooks  >= noofbooksf 
order by noofbooks desc; 

That is what I have so far, it is optimized to high 80's however the two joins are increasing the cost. Therefore what I am trying to do is just do the join once. Then use the result to get the third element of it. I thought about creating a temp View, but i'm sure there would be better ways out there.

Thanks.

Answer 1

If you want the persons that have the first or second most borrowed books, then just do:

select c.f_name, c.l_name, count(lb.customerid) as noofbooks 
from customer c left join
     loaned_book lb
     on c.customerid = lb.customerid 
group by c.f_name, c.l_name
order by noofbooks desc
limit 3;

If you are concerned about ties:

select f_name, l_name, noofbooks
from (select c.f_name, c.l_name, count(lb.customerid) as noofbooks,
             rank() over (order by noofbooks desc) as seqnum
      from customer c left join
           loaned_book lb
           on c.customerid = lb.customerid 
      group by c.f_name, c.l_name
     ) clb
where seqnum <= 3;