1867.orders-with-maximum-quantity-above-average.md

1867. Orders With Maximum Quantity Above Average

Solution 1: Group by, Max, Avg, All, Having.

Group by order_id to find the maximum quantity for a particular order.
ALL means that the condition will be true only if the operation is true for all values in the range.
If the maximum quantity of an order is bigger than ALL the average quantities for each order, select that order_id.

Runtime: 804ms

SELECT DISTINCT order_id
FROM OrdersDetails
GROUP BY order_id
HAVING MAX(quantity) > ALL(
  SELECT AVG(quantity)
  FROM OrdersDetails
  GROUP BY order_id
);

Solution 2: Logic

If the maximum quantity needs to be bigger than ALL average quantities,
it essentially means that it just needs to be bigger than the MAXIMUM average quantity.
Therefore, select only the maximum average quantity and compare it with the maximum quantity for each order.

Runtime: 485ms

SELECT DISTINCT order_id
FROM OrdersDetails
GROUP BY order_id
HAVING MAX(quantity) > (
  SELECT AVG(quantity)
  FROM OrdersDetails
  GROUP BY order_id
  ORDER BY 1 DESC
  LIMIT 1
);