Problem

Given a table of data representing customer orders, write a SQL query to find the order with the highest total value.
Input:
| customer_id | order_id | item | quantity | price |
| ------------|----------|------|----------|-------|
| 1           | 1001     | abc  | 2        | 10.00 |
| 1           | 1002     | xyz  | 1        | 20.00 |
| 2           | 1003     | pqr  | 10       | 5.00  |
Output:
| customer_id | order_id | total_value |
| ------------|----------|-------------|
| 1           | 1002     | 20.00       |

Solution

SELECT customer_id, order_id, SUM(price * quantity) AS total_value
FROM orders
GROUP BY customer_id, order_id
ORDER BY total_value DESC
LIMIT 1;

/*

The solution above is optimal because it uses the GROUP BY clause to group the data by customer_id and order_id, and then uses the SUM function to calculate the total value of each order.

The solution then uses the ORDER BY clause to sort the data by total_value in descending order, and then uses the LIMIT clause to return only the first row.

*/