I want to filter top 2 customers with their top 2 products they had ordered over a period.
| Date | Customer | Product | Qty | Sales |
|---|---|---|---|---|
| 1/10/2023 | Mike | Apple | 1 | 3.00 |
| 1/10/2023 | Joyce | Apple | 1 | 3.00 |
| 2/10/2023 | Naomi | Orange | 1 | 2.00 |
| 2/10/2023 | Naomi | Apple | 1 | 3.00 |
| 3/10/2023 | Naomi | Orange | 1 | 2.00 |
| 3/10/2023 | Mike | Orange | 1 | 2.00 |
| 4/10/2023 | Naomi | Banana | 1 | 1.00 |
| 4/10/2023 | Mike | Orange | 1 | 2.00 |
| 4/10/2023 | Mike | Grapes | 1 | 1.00 |
| 5/10/2023 | Mike | Orange | 1 | 2.00 |
| 5/10/2023 | Helen | Orange | 1 | 2.00 |
See codes that l have tried
`SELECT
TOP 5 O.Customer, O.CustomerId
FROM Orders O
JOIN (
SELECT Customer, TOP 2 Product, Sum(Sales) as Sales
FROM OrderDetail O2
GROUP BY Customer, Product
ORDER BY SUM(OrderDetail.Sales) DESC
) O2 ON O2.CustomerId = O.CustomerId
ORDER BY O.CustomerId
`
Expected output
| Customer | Total Sales | Orange | Apple |
|---|---|---|---|
| Naomi | 9.00 | 6.00 | 3.00 |
| Mike | 7.00 | 4.00 | 3.00 |