Search code examples
sqlpostgresqlgreatest-n-per-group

SQL: Find nth order for nth customer

I am quite new to SQL, have been learning for ~3 weeks, and have taken a liking to it. Hoping to polish up my skills before beginning to apply to Data Analyst roles.

I've been working with a dummy dvd-rental database and have found myself unable to solve a challenge given to me by a peer. The question was: "what is the most expensive rental for the 4th customer?"

enter image description here

We can see in picture, that based on the nth_customer column, Terrance Roush is the 4th ever customer (he's the 4th ever person to pay). But the issue is that the nth_customer column is actually reporting back the nth order and continues counting to infinity. So the next time Terrance shows up, the nth_customer column will not show '4' (which is what I was hoping to achieve).

Would appreciate any feedback on how to solve this. Thank you in advance.

Solution

  • If "the fourth customer" means the customer who did the fourth rental, you can break the problem down into two - finding that fourth customer, and finding their most expensive rental. Something like this:

    SELECT * 
FROM payment 
WHERE customer_id = (
    SELECT customer_id 
    FROM payment 
    ORDER BY payment_date 
    LIMIT 1 OFFSET 3
) 
ORDER BY amount DESC
LIMIT 1;

    Here I'm finding the ID of the fourth customer in the subquery, using a LIMIT & OFFSET to get just the one record I want. Then in the outer query I'm simply ordering all of that customer's records and taking the one with the biggest amount.