Orders With Maximum Quantity Above Average - Leetcode Solution

Problem:

Given a table orders with columns order_id, customer_id, quantity.

You need to write an SQL query to find the customer_id and the order_id which have the maximum quantity above the average quantity of all the orders.

Return all of them in any order.

Solution:

In order to solve this problem, we first need to find the average quantity of all the orders. We can do that by using the AVG function in SQL. Once we have the average quantity, we need to find the maximum quantity above the average. We can achieve this by using the MAX function in SQL.

To find the customer_id and the order_id corresponding to the maximum quantity above average, we need to perform a join operation between the orders table and a subquery. The subquery will return the maximum quantity above average and we will join it with the orders table on the condition that the quantity in the orders table is greater than the average quantity. Once we have this join, we can select the customer_id and the order_id from the orders table.

The SQL query for the above problem is given below:

SELECT customer_id, order_id FROM orders JOIN (SELECT MAX(quantity) AS max_qty FROM (SELECT quantity, AVG(quantity) OVER() as avg_qty FROM orders) temp WHERE quantity > avg_qty) AS subquery ON orders.quantity = subquery.max_qty

Explanation:

The above query first calculates the average quantity by using the AVG function and then finds the maximum quantity above average by using the MAX function. This is done in a subquery. We then join this subquery with the orders table by using the JOIN keyword and the ON clause. The condition for joining is that the quantity in the orders table is greater than the average quantity.

Finally, we select the customer_id and the order_id from the orders table. The result set will include all the customer_ids and order_ids that have the maximum quantity above average.

Note: This solution assumes that there are no ties for the maximum quantity above average. If there are ties, then this solution will only return one of the tied customer_id and order_id combinations.

1