Pull each customer's top-2 orders for the VIP report
A VIP report wants «the two most expensive orders per customer» — for each customer separately, their top two by amount. Numbered windows solve this trivially, but there's a more expressive trick: a join where a limited subquery sees the outer row's columns and runs separately for each of them. Build the report without window functions — for every customer show the customer id, the order id, and the amount, limited to the two largest orders per customer. Sort by customer id, ties broken by amount descending.
Expected output sample
This is what a correct answer looks like — its row count is its own, it doesn't have to match the schema tables.
| id | amount | customer_id |
|---|---|---|
| 3 | 300 | 1 |
| 2 | 200 | 1 |
| 4 | 500 | 2 |
Sign in to see submission history
Sign inSign in to use AI Mentor
Sign in