我对SQL还是很陌生,已经学习了大约3个星期,并且喜欢它。希望在开始申请Data Analyst角色之前先提高自己的技能。
我一直在使用虚拟DVD出租数据库,但发现自己无法解决同龄人给我的挑战。问题是:“第四位客户最昂贵的租金是多少?”
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.
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;
在这里,我在子查询中找到了第四个客户的ID,使用LIMIT&OFFSET来获取我想要的一个记录。然后在外部查询中,我只是对所有该客户的记录进行排序,并选择数量最大的记录。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句